google sheets, making a dropdown list based on user input

Question

I am working on a scheduling system for a very specific process. I am going to be sending out some shifts to my employees, and based on the result of their input, I want to create a dropdown for the employees that said they could work the shift.

G2:E2 are sample employees, and assuming only fred and test said they could work the June 10 shift, the dropdown for that shift should only show fred and test. Right now it is set to show all of them, but I want to create a custom formula that will allow me to check if the cell under their name next to the corresponding shift is empty, and if it's not, add their name to the dropdown. If no one can take it, the dropdown should stay empty. The reason I am checking if it's empty is because I dont want to have to set restrictions on the input of the cell, as I have a lot of employees and they would either forget or not respect them.

How would I go about getting ONLY the employees who said they could work to show up on the dropdown, so I can easily see who wants to work each shift.

Answer 1

Use an auxiliary range

---

Google Sheets data validation could use a custom formula to accept or reject a value but not to set a list of valid values that are shown in a dropdown, but it could use values from a range.

There are several ways to do this. i.e.

1. Transpose the values
2. Use FILTER

 

=FILTER(TRANSPOSE(G2:K2),TRANSPOSE(G5:K5))

Add the above formula to a range to be used as reference, like L1:L5, then set this range in your data validation rule.