How do I...? Create Dynamic Drop Down Data Validations

Data Validation with a drop down list has many uses.


On the help forums, VWH asked "Can I create dynamic popups?" VWH wants to assign individuals to certain tasks where each individual can only be assigned once.


In order to accomplish this we need an array of the available initials.  Next to this will be a column that checks to see if the person has already been assigned.

Notice, that the answer will be "Yes" or "No."  We will then query the table we just created to show only the values in column 1 (the initials) where column 2 is "No" (indicating that initial has not been assigned).  This new array will be what is used for the Data Validation with drop downs.  This array will dynamically update as initials are assigned.

For this to work the validation must allow invalid values (any values that isn't on the list).  If this is not checked the value you choose will disappear as soon as you choose it.  This is because the value is no longer on the validation list and therefore invalid.  Invalid values are not allowed and will disappear.  The only work around is to allow invalid values.

See my Templates Pages for your own copy!  Comments welcome!

Cheers!