using conditional named range in excel
Let me try this again, I've been searching the web and can't seem to find anything. What i'm trying to do is in excel have a bunc of named range list conditional based on a value of another cell. The problem I'm running into character limit when I setup the data validation and put all my IF statements in source textbox. If i just put two conditions it seems to work fine, but that won't work for me. Here is my example data, based on the B column value I want to display the valid sub-items for it. Like I mentioned I have 20 or so different possible values that could be in column B.
B C
1 ENG dropdown of all engineering sub-categories
2 PRO dropdown of all production sub-categories
3 PER ...
4 PAY
5 ENG dropdown of all engineering sub-categories
6 ENG dropdown of all engineering sub-categories
When i try to setup my data validation list on column C1, I put in the following...
=IF($E$5="CAR", CA,
IF($E$5="DCC", DCC,
IF($E$5="ENG", ENG,
IF$E$5="ENV", ENV,
IF$E$5="FBI", BI,
IF$E$5="FGL", GL,
IF$E$5="FAP", AP,
IF$E$5="FRE", AR,
IF$E$5="FTX", Tax,
IF$E$5="ORM", OAR,
IF$E$5="PAY", PAY,
IF$E$5="PIR", PER,
IF$E$5="PRO", PRO,
IF$E$5="PUR", PUR,
IF$E$5="RSK", RM,
IF$E$5="SLM", Sales,
IF$E$5="WFS", WAR)))))))))))))))))
I'm only able to type in maybe half of my IF condition. I tried VLOOKUP but that only allows for 1 value to be put in column C, and I want it to be a dropdown. Any help would be much apprecriated.
Answer
You can do this in a much shorter formula by combining VLOOKUP and INDIRECT:
Have you list of of potential names in column B and their corresponding range names somewhere in two columns, e.g. column X & Y:
Col X Col Y CAR CA DCC DCC ENG ENG ENV ENV FBI BI ...
Then use the following formula as the source of the list in the Data Validation:
=INDIRECT(VLOOKUP($E$5,$X:$Y,2,0))
Actually, with this approach you can potential even save the effort of naming and maintaining the manual range names! Instead, just replace the name of the named range with the real address - which - with a bit of luck and smart formula engineering - you can derive automatically. This depends on your data structure, but most likely the functions ADDRESS, COUNTA, OFFSET, INDEX and MATCH will be helpful.
E.g. if your lists would be stored in another worksheet with the name of the list in the first row and the elements listed below each header, this example file will provide you the example formulas. One step further, it'll also use conditional highlighting to mark any entry that is invalid, e.g. because the type was changed after the selection:
