How to disallow a selection within drop down without restricting it in ValidIF

Hello, I have a client who would like to display all possible products in a drop down so her team knows the product has been setup in the system but if the product is being serviced in the maintenance department she doesn’t want the user to be able to select the product.

I can use color to indicate the product is in maintenance and I understand we’re able to filter out the options with a select statement in the Valid If but unfortunately users need to see the product has been setup in the system.

Is there any way to do this in the system?

Yes you can do that. Read the list with the Suggested values and then check the validation with the valid If.


My first thought is to try Suggested values to generate the dropdown list (using the same expression you might otherwise use with Valid If), then use Valid If to validate only.

The Valid If expression would have to be carefully crafted to return a Yes/No value–not a list–and must not consist solely of an IN() expression. An IN() expression alone will trigger the magic that produces a dropdown menu and would confuse the Suggested values. A common way to avoid the magic is to wrap the IN() expression in AND() or OR():

  • AND(IN(...), TRUE)
  • OR(IN(...), FALSE)

See also:


Got it will try this today as well. Thanks again

1 Like