I've been trying to adapt a few other approaches to this, but without much luck.

I want to create an approval dropdown. Each request could have up to five rows, but I want just a singular dropdown to display the Approval Status on the first line of the request. A mockup of what I'm thinking:

mockup example

In my example, I'd like to check A2 to see if it has any data. Then if it does, show the approval status data validation dropdown (in ColumnJ) with the options Pending, Approved, Denied. The problem I've run into is that if I use a custom formula in the data validation, it doesn't allow me to create a list of options in the dropdown (or create a list from a range) that would show the dropdown with the arrow, or at least I haven't found a formula that will allow me to do that.

Is this even possible, or are the list and custom formula criteria not compatible?

1 Answer 1


Using a custom formula for validation never creates a dropdown. Dropdowns are available only for lists of values that are either entered directly into the validation rule, or are contained elsewhere in a sheet ("list from a range").

Possible workaround: use "list from a range" validation rule, referring to a range in which you use some formulas to compute the available options.

Example: cell B3 has validation rule saying "list from the range P3:R3". Cell P3 has the formula =if(isblank(A3), "", {"aa", "bb", "cc"}). So, if A3 is nonempty, than the range P3-R3 contains "aa", "bb", "cc", and these values appear in the dropdown in B3. Otherwise the range is blank, and so is the dropdown.

  • Why are there no dropdowns for custom formulas? There are formulas that create ranges such as FILTER and QUERY Commented Sep 2, 2018 at 11:00

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.