using conditional named range in excel

Question

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.

Peter Albert · Accepted Answer · 2013-02-24 21:43:00Z

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:

enter image description here

Collectives™ on Stack Overflow

using conditional named range in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related