Conditional Data Validation

[Scott24x7]

Hi all,
I'm wondering if there is a way to do a conditional drop-down list, based on data in the cell next to it.
The idea is for simplified expense report. So we have 3 categoreis of expense:
Transportation
T&E
Corporate

So when say cell C3 = Transportation, then cell C4 dropdown should present:
Taxi
Train
Flight
Bus
Rental car

If C3 = T&E it should present:
Meal
Drinks
Client Entertainment

If C3 = Corporate it should present:
Hardware
Software
License
Office Supply


So I use a separate page for lookup of these types, so I can add/reduce them over time if they become irrelevant. But I can only validate them in "C4" either as one giant list, or I have to make C4 for transport, C5 for T&E C6 for Corporate Expenses.

I thought maybe there is a way to add a field in the validation page next to each type, and then take them all but filter on them, but that didn't work in a dynamic way.
Can anyone suggest an elegant and maintainable way for conditional drop-down selection?
Many thanks

Oh, and I prefer to avoid macro and do only in-cell if at all possible. If it can only be done with Macro, then I would use it though.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Hi all,
After working a bit more with this, I found the solution. Using Named Ranges (Dynamicly created as well, so lists can easily get longer or shorter with INDIRECT and OFFSET function), then in the Data Validation for the cell, I use this formula with LIST selected:

=IF(C3="Transportation",DynamicTransport,IF(C3="T&E",DynamicTandE,DynamicCorporate))

I know the second one is kind of cheating, but if C3 is blank, then the dropdown will just match that, and since I have data validation on C3 on a drop down to be only those 3 values, or blank, it's rather ok... I guess I could create a third IF, and make the last option be "" so when C3 is also blank, I would have a blank list... maybe I'll do that.

Cheers

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Ah, wait, that didn't work. It fooled me. When I added the second IF it bombed, and the first IF is always giving me the same (last) condition...
So looks like I still need a solution. :/

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

It turns out I did have it right, but the drop-down item which as a label "T&E" was fine, when it got inserted into the "FI" clauses it caused problems. The final code on the Data Validation now looks like this:


=IF(C19="Corporate",DynamicCorporate,IF(C19="Transportation",DynamicTransport,IF(C19="T and E",DynamicTandE," ")))

So we just changed T&E to be T and E and it worked!
And when the prior cell is blank, then it only presents a blank dropdown.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[zelgar]

Instead of you way, for the 2nd Data Validation use the INDIRECT command. For example, =Indirect(C3) instead of your IF(C3...