How to create a single dynamic drop-down list conditioned on values of three other cells on other sheets?

[Rhys2]

Hi Everyone!

I am attempting to develop what at first seemed to be a simple scheduling aid in excel 2016 – but can’t seem to find a way to populate a single dropdown list with a conditioned list based on a of range variables on different sheets in the workbook.

The first and primary sheet, Staffing, has days across in columns and lines of service in rows …



The second sheet has a list of service providers in rows and the list of service lines in columns in an x by y matrix which indicates weather the service provider is skilled or not to provide the service. A '0' (zero) meaning not skilled and therefore will not be included in the final list and greater than zero meaning skilled …


The third sheet has a list of the service providers in rows and the service lines in columns which indicates the days the service provideris schedule to work …


The goal is to have on the first (staffing sheet) a dynamic drop-downlist drop down for each service line for each day to assign a service provider that includes all the possible (skilled from second sheet)candidates and are scheduled to work (from the Work Schedule sheet) for the selected service-line for that day. This would be a subset of the complete list of service providers based on data from the secondsheet (Service Providers & Service Lines) further conditioned by the thirdsheet (Work Schedule) sheet.


I attempted to accomplish this using Data Validation,selecting custom and putting a expression in the function field to no avail. Ideally (I think an sql like query in a custom data validation would do the trick but I cat get it to work.


How can this be accomplished in excel?

Thanks!!

 

[Logit]

.
I hope I understand your question correctly . ....

What about using a list for the dropdown, that references the other sheet data ?

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Sheet2 U1[/td][td] <-- dropdown[/td][td][/td][td]Sheet1 T1[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td][/td][td][/td][td][/td][td]Sheet1 T2[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td][/td][td][/td][td][/td][td]Sheet1 T3[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td][/td][td][/td][td][/td][td]Sheet2 U1[/td][td][/td][td]<-- formula : =Sheet2!U1[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td][/td][td][/td][td][/td][td]Sheet2 U2[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td][/td][td][/td][td][/td][td]Sheet2 U3[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td][/td][td][/td][td]Sheet3 V1[/td][td][/td][td]<-- formula : =Sheet3!V1[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td][/td][td][/td][td][/td][td]Sheet3 V2[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td][/td][td][/td][td][/td][td]Sheet3 V3[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Rhys2]

Hi Logit! Thanks for the quick response! I can't get images to post to help explain what I am attempting to accomplish - so I will continue explaining in text.

The Staffing sheet has a list of service lines in rows down the left hand side of the sheet and a years worth of dates in columns across the top. The goal is to have a drop down at each of the intersections of service line and day of the year that produces a list of possible candidates that are trained for each of the specific service line determined from the second sheet - "service lines and service providers" - a matrix of service providers vs. service lines - and are working that day based on the third sheet - Working Schedule- another matrix of service provider vs. day of the year.

When the dropdown for a specific day on the staffing sheet is clicked it will look at the second and third sheet matrixes for that specific day and yield only the personal that are trained and available to work that
day.

Hope this better explains the goal.