Nested IF Statement Question

[EleKtriKaz]

Hi,

I discovered a while back that Excel will only let you nest up to 7 IF statements. This makes no sense to me, and I can't even come up with a reason as to why Microsoft would continue to support this limitation with the processing power of computers today...but I digress.

My problem is that I have a worksheet with a drop down list that has about 30 items in it. I'm trying to construct the sheet so that when one of the items in the list is selected it will populate a few other cells.

Basically, the list is a list of text items. When one of the text items in the list is selected I would like it to populate a specific number into a different cell based on that specific text item.

I can't figure out a way around the 7 nested IF statement limitation. Any help would be greatly appreciated.

[Bryan Hessey]

Hi,

The attached example shows various ways to use the Offset to pickup related data when an item is selected from a Listbox or Combo box etc

Hope this helps

Hi,

I discovered a while back that Excel will only let you nest up to 7 IF statements. This makes no sense to me, and I can't even come up with a reason as to why Microsoft would continue to support this limitation with the processing power of computers today...but I digress.

My problem is that I have a worksheet with a drop down list that has about 30 items in it. I'm trying to construct the sheet so that when one of the items in the list is selected it will populate a few other cells.

Basically, the list is a list of text items. When one of the text items in the list is selected I would like it to populate a specific number into a different cell based on that specific text item.

I can't figure out a way around the 7 nested IF statement limitation. Any help would be greatly appreciated.

[Greg Wilson]

Presumably the dropdown is populated from a range on the worksheet such as a
Data Validation in-cell dropdown. Enter the desired values associated with
each of these text items in the adjacent column and use the VLookUp function.
Do you really want to use a 30 level nested If function ?

Alternatively, you can create a named array. Example:
1. Select Insert > Name > Define
2. Enter "MyList" in the "Names in workbook" window
3. Enter the following array in the "Refers to" window:
={"A",1;"B",2;"C",3;"D",4;"E",5;"F",6;"G",7;"H",8;"I",9;"J",10}
4. In the desired cell enter the formula:
=VLookUp(MyList, "G", 2)
The formula in this case will return 7 which is the number associated with
"G" in the array.

Note that in the above array, column delimitation is established with commas
and row delimitation is established with semicolons. Saves on real estate.

Regards,
Greg

"EleKtriKaz" wrote:

>
> Hi,
>
> I discovered a while back that Excel will only let you nest up to 7 IF
> statements. This makes no sense to me, and I can't even come up with a
> reason as to why Microsoft would continue to support this limitation
> with the processing power of computers today...but I digress.
>
> My problem is that I have a worksheet with a drop down list that has
> about 30 items in it. I'm trying to construct the sheet so that when
> one of the items in the list is selected it will populate a few other
> cells.
>
> Basically, the list is a list of text items. When one of the text items
> in the list is selected I would like it to populate a specific number
> into a different cell based on that specific text item.
>
> I can't figure out a way around the 7 nested IF statement limitation.
> Any help would be greatly appreciated.
>
>
> --
> EleKtriKaz
> ------------------------------------------------------------------------
> EleKtriKaz's Profile: http://www.excelforum.com/member.php...o&userid=33141
> View this thread: http://www.excelforum.com/showthread...hreadid=529503
>
>

[Greg Wilson]

Correction:

The formula that retrieves the associated value from the array should be:
=VLookUp("G", MyList, 2)
Alternatively, =VLookUp(J4, MyList, 2) where cell J4 = "G".

Greg

"Greg Wilson" wrote:

> Presumably the dropdown is populated from a range on the worksheet such as a
> Data Validation in-cell dropdown. Enter the desired values associated with
> each of these text items in the adjacent column and use the VLookUp function.
> Do you really want to use a 30 level nested If function ?
>
> Alternatively, you can create a named array. Example:
> 1. Select Insert > Name > Define
> 2. Enter "MyList" in the "Names in workbook" window
> 3. Enter the following array in the "Refers to" window:
> ={"A",1;"B",2;"C",3;"D",4;"E",5;"F",6;"G",7;"H",8;"I",9;"J",10}
> 4. In the desired cell enter the formula:
> =VLookUp(MyList, "G", 2)
> The formula in this case will return 7 which is the number associated with
> "G" in the array.
>
> Note that in the above array, column delimitation is established with commas
> and row delimitation is established with semicolons. Saves on real estate.
>
> Regards,
> Greg
>
> "EleKtriKaz" wrote:
>
> >
> > Hi,
> >
> > I discovered a while back that Excel will only let you nest up to 7 IF
> > statements. This makes no sense to me, and I can't even come up with a
> > reason as to why Microsoft would continue to support this limitation
> > with the processing power of computers today...but I digress.
> >
> > My problem is that I have a worksheet with a drop down list that has
> > about 30 items in it. I'm trying to construct the sheet so that when
> > one of the items in the list is selected it will populate a few other
> > cells.
> >
> > Basically, the list is a list of text items. When one of the text items
> > in the list is selected I would like it to populate a specific number
> > into a different cell based on that specific text item.
> >
> > I can't figure out a way around the 7 nested IF statement limitation.
> > Any help would be greatly appreciated.
> >
> >
> > --
> > EleKtriKaz
> > ------------------------------------------------------------------------
> > EleKtriKaz's Profile: http://www.excelforum.com/member.php...o&userid=33141
> > View this thread: http://www.excelforum.com/showthread...hreadid=529503
> >
> >

[Bob Phillips]

"EleKtriKaz" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I discovered a while back that Excel will only let you nest up to 7 IF
> statements. This makes no sense to me, and I can't even come up with a
> reason as to why Microsoft would continue to support this limitation
> with the processing power of computers today...but I digress.

Because it is bad coding and there is always another way, It's easy to
non-thinkingly just add another condition, and then suddenly the spreadsheet
is as slow as a tortoise, and you post saying why do MS sustain such slow
spreadsheets.


> My problem is that I have a worksheet with a drop down list that has
> about 30 items in it. I'm trying to construct the sheet so that when
> one of the items in the list is selected it will populate a few other
> cells.

This sounds like a clssic case for a table of values, and use VLOOKUP to
retrieve using the selected value. Help has plenty of detail.

[Patricia Shannon]

Depending on what you're doing, another possibility is the "Select Case"
statement.
Look up "case" or "select case" in help It's a good thing to know about
even if it's not what you need here.

"Bob Phillips" wrote:

>
> "EleKtriKaz" <[email protected]> wrote
> in message news:[email protected]...
> >
> > Hi,
> >
> > I discovered a while back that Excel will only let you nest up to 7 IF
> > statements. This makes no sense to me, and I can't even come up with a
> > reason as to why Microsoft would continue to support this limitation
> > with the processing power of computers today...but I digress.
>
> Because it is bad coding and there is always another way, It's easy to
> non-thinkingly just add another condition, and then suddenly the spreadsheet
> is as slow as a tortoise, and you post saying why do MS sustain such slow
> spreadsheets.
>
>
> > My problem is that I have a worksheet with a drop down list that has
> > about 30 items in it. I'm trying to construct the sheet so that when
> > one of the items in the list is selected it will populate a few other
> > cells.
>
> This sounds like a clssic case for a table of values, and use VLOOKUP to
> retrieve using the selected value. Help has plenty of detail.
>
>
>

[EleKtriKaz]

Thanks for the responses guys. Very helpful.

Vlookup! Why the heck didn't I think of that?!

I use that function on a daily basis.

Thanks again for the help.