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.

 

[Guest]

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

 

[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.

+-------------------------------------------------------------------+
|Filename: Combo.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4579 |
+-------------------------------------------------------------------+

 

[Guest]

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

 

[Bob Phillips]

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.

 

[Guest]

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.

 

[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.