This is actually pretty simple, it just took me about a half hour to figure
out what the syntax is for the IF formula you put in your secondary (or
tertiary, or quaternary, whatever) dropdown list validation cell.
Example: I have a basic list of general property types: Retail, office and
industrial. In cell B1, I want the user to select one of the 3 types. In
B2, though, I want her to select the SUBtype; i.e., if she selects Retail in
B1, I want her to see only the retail subtypes in B2. If she selects Office
or Industrial in B1, I want her to see only the Office or Industrial subtype
list when she clicks the B2 dropdown. Let's say I've created the four
(General, Retail, Office and Industrial) dropdowns in another sheet in the
same workbook and have given each range the names George, Ronald, Oscar and
Izzy, though giving them the same names as the categories is okay, too).
Then I go back to cell B1 in my main sheet and select Data | Validation
(Excel 2007), choose List and write in the formula, =George. When done, if I
select cell B1, I'll have a dropdown that lists Retail, Office and
Industrial. Then I go to cell B2, select Data | Validation, choose List,
and--here's the secret--enter an IF formula as follows:
There are 3 IF statements, so you have to have 3 closing parens on the end,
of course. But the trick is to identify the text in B1 in quotes (because
text is what Excel will see there), and the named range WITHOUT quotes,
because Excel sees it as a range address, not as text. The formula really
won't return "Wrongamundo, Buckwheat!" if the user fails to select one of the
three types, because those are the only ones I allowed. But I could have
allowed a user-created entry.
Pretty slick, eh? The cool part is you can create as many dropdown levels
as you have levels of patience to enter long, nested "IF" formulas...