Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Statement Question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Statement Question
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Statement Question
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 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. +-------------------------------------------------------------------+ |Filename: Combo.zip | |Download: http://www.excelforum.com/attachment.php?postid=4579 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=529503 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Statement Question
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Statement Question
"EleKtriKaz" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Statement Question
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Statement Question
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. -- EleKtriKaz ------------------------------------------------------------------------ EleKtriKaz's Profile: http://www.excelforum.com/member.php...o&userid=33141 View this thread: http://www.excelforum.com/showthread...hreadid=529503 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF question | Excel Worksheet Functions | |||
Pivot table question | Excel Discussion (Misc queries) | |||
Nested IF statement with cell range reference | Excel Worksheet Functions | |||
IF Statement question | Excel Worksheet Functions | |||
If Statement Question | Excel Worksheet Functions |