Data Validation...Can't get INDIRECT to work

[telewats]

Hello Everyone,

I'm attempting to create drop down boxes where each subsequent box is dependent on the one before. I know I need to use Data Validation, and, further, the Indirect formula for the subsequent boxes. I've even read many of the prior posts, tips of the day, etc. concerning the use of this, but I am obviously doing something wrong b/c I cannot get it to work in my spreadsheet.

Let's see if I can explain exactly what I'm trying to do:

In Cell D46, I want a dropdown box allowing a selection of States (AR, AL, MO, OK, etc.), which I have listed in Column L.

In Cell D47, I want a dropdown box that, based on the State selected in D46, allows a selection of Locations within that state (ECLC, MOOD, BEVL, etc.), which I have listed in Column M.

In Cell D48, I want a dropdown box that, based on the Location selected in D47, allows a selection of Classifications within that Location (CBLE, CO, IR, etc.), which I have listed in Column N.

And, then, ultimately, I'd like to combine the selections chosen above as one word in Cell D49 (i.e. ALMOODCBLE).

I've named my ranges as described in the threads I've reviewed, and I've gotten Cell D46 to work fine (State dropdown box). However, every time I try to use the =INDIRECT as the Source for the Validation in Cell D47, I get an error.

Can someone please help? I'm only lukewarm when it comes to Excel, so this one seems to be beyond me.

Thank you so much!!

 

[Jonmo1]

It get's pretty deep for 3 tiered dependant dropdowns, but here's how it works...

you need to have your main list of States
then have a sperate list for EACH STATE'S Locations. 1 list for AL, 1 for AK, 1 for AZ.
Each of those should be NAMED using Insert - Name - Define. Using the Name in the list (AL and AK and AZ etc..)

then on top of that a seperate list for EACH State's Locations classification, 1 for AL CBLE, 1 for AL CO , 1 for AK CBLE, 1 for AL CBLE etc..And those should be named using Insert - Name - Define..

Then your indirect will work in the Dropdowns.


Sorry, wish it was easier.. But dependant dropdowns are NOT easy ..

 

[telewats]

This did take some time, but it worked. Thanks for the help jonmo1 !