Drop Down List Selection Based On Column Title Selection

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.