Drop Down List Selection Based On Column Title Selection

[StephenCoff]

G'day Peeps,
Very limited skill and have a feeling can't be done, perhaps someone smarter has a far better solution to achieve the objective. I'm appreciative and open to any suggestions or help. Hopefully this will make sense as to my objective:

Trying to make an excel sheet for onsite auditing with simple drop down lists.
So i have a "Data" tab that has all my drop down list data.
I can Define Name, data Validation and make the list, the basics I'm fine with. Where I'm stuck:

In "D2" i select the service type - Mechanical.
In "E2" I want the drop down list to show all the data in tab Data!F7:F34.

If "D2" was selected as say "Fire"
I want "E2" drop down list to show all the data in tab Data!D7:D34

I'm not sure if it can be done or how, was wondering if you can use formula's in the "Source" line creating the drop down list in Data validation. If you can, what formula to even create such a selection.



Hopefully I've attached template properly & post makes sense.

[kvsrinivasamurthy]

Pl see file
Four named ranges are created
Headers are the names

Formula for Data Validation in F10

=INDIRECT(INDIRECT("'Passive Fire'!$D$10"))

[torachan]

Easiest way is to use data entry UserForm - with filtering comboboxes where needed to drill down relative elements.
Examples attached of universal form - data entry/edit/update/delete - small example form of cascading comboboxes.
the sheet then just acts as a pseudo database - there are no on-sheet formulas or data validation.
torachan.