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.here is waht I am talking about

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.

  • Welcome. Because it is a dropdown, I don't think you can do that without using a script.
    – Tedinoz
    Oct 22, 2019 at 14:56
  • With a Google Apps Script this should definitely be possible to do! Oct 22, 2019 at 16:28

1 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



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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.