title: using conditional named range in excel
body: <p>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.  </p>
  
  <pre><code>          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
  </code></pre>
  
  <p>When i try to setup my data validation list on column C1, I put in the following...</p>
  
  <pre><code>=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)))))))))))))))))
  </code></pre>
  
  <p>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.</p>