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:
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?
Best Answer
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.