Forum Discussion

deMonthuNder's avatar
deMonthuNder
Copper Contributor
Nov 09, 2024

Multiple dependent drop down list issue

I'm attempting to make a log sheet that tracks customer service survey results for my employer. I've chosen to use dependent drop down lists for the log in order to refine the sorting process as efficiently as possible. Unfortunately, I've hit a "road block" that i cant find the solution for. My log is as follows...

Driver                       Service Day                           Customer

Driver 1                     1st Monday                            Customer 1

Driver 2                     1st Tuesday                            Customer 2

Driver 3                     1st Wednesday                      Customer 3

etc.                            etc.                                         etc.

 

For clarity, each driver has 20 service days (Monday-Friday, weeks 1-4) and each service day contains up to 30 unique customers per driver. 

I've created the drop down lists using the INDIRECT function to refer to the value chosen in the previous data validation so i can choose the Driver then the Service Day on his/her route (eg. Column A2 contains the Drivers. Column B2 uses =INDIRECT(A2) in the data validation to reference the Service Day for the selected driver). I can do the same for the Customers drop down by using =INDIRECT(B2) in the data validation, but this only works if i have just 1 driver UNLESS i use "named ranges" for each drivers service days because there are multiple routes on the same service day (eg. Driver1FirstMonday, Driver1FirstTuesday, etc.).

Is it possible to use the INDIRECT function to refer to both previous cell references (A2 and B2) in the data validation of the Customers drop down list so than only the customers for the specified driver and specified day show up in the third drop down without having to use named ranges specific to each drivers' route?

Additionally, once I've chosen the desired Driver, Service Day and Customer, i need to attach the survey results for the chosen customer to the Log somehow so my employer can identify who has/hasn't been surveyed. All suggestions for this are welcome.

 

Thanks in advance for your assistance with this challenge!

 

 

Resources