Multiple Dropdown lists

[march]

Hi All,

Hoping you can help with the following please. I need to be able to present a list of engineers capable of performing a role based on the support area and the level of experience. To add an extra element, some of the engineers are capable in multiple areas.

I've created a number of named lists such as:
Support_Area : Server, Network, Storage
Role_Level: Junior, Mid, Senior
Along with individual named lists which contain all the engineers for each support_area, role_level. I.e.
Server_Junior {Engineer 1, Engineer 2} ; this references A3 down
Server_Mid {Engineer 3, Engineer 4}, this references B3 down
Storage_Junior {Engineer 2, Engineer 12} ; this references I3 down

What I have so far a drop-down in Column C (Support_Area) , followed by drop-down in Column D (Role_Level), the Column E (Engineer) needs to present a drop-down list (such as Server_Junior) of the relevant engineers capable of the earlier 2 selections.

How would I go about getting the column E populated with a drop-down list without putting in a rather excessive IF statement for every scenario?

If it's not possible to do it the way I've currently go it, what are the alternatives?

Appreciate any help.

Cheers,

March

 

[march]

Sorry not sure how to edit my original post.
This is what the messy IF statement would look like:

=IF(AND(C2="SERVER",D2="Junior"),SVR_JNR,IF(AND(C2="SERVER",D2="Mid"),SVR_MID,IF(AND(C2="SERVER",D2="Senior"),,IF(AND(C2="NETWORK",D2="Junior"),NWK_JNR,IF(AND(C2="NETWORK",D2="Mid"),NWK_MID,IF(AND(C2="NETWORK",D2="Senior"),NWK_SNR,IF(AND(C2="STORAGE",D2="Junior"),STO_JNR,IF(AND(C2="STORAGE",D2="Mid"),STO_MID,IF(AND(C2="STORAGE",D2="Senior"),STO_SNR,))))))))

Problem is it's waaaay to big for data validation to present back a list, which then also has the other side effect of not being scalable at all so if i do increase the number of support areas i can't in this scenario at all (not that I can now anyway as the above formula is over 250odd characters)

Any help would be massive.
Thanks All,

March