How to allow a hex code to be inputted into an existing dropdown list of values- Data validation (Google sheets/apps script)

0
votes

To summarise, I have a column called "Color" - I need a dropdown list that accepts a value (red,blue,green etc) by selecting a colour from a pre defined list and also for ANY hex color code to be inputted manually into a cell by the user - Currently the user is getting an invalid flag when inputting hex codes as hex codes are not in the data validation list for the drop down.

Is it possible to set up a dropdown list programatically using app scripts and for the dropdown validation to allow a hex code or colours.

1
javascriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-macros
I think data validation can't be combined, we must select one of them: list range, list item, custom formula etc - user11982798

1 Answers

0
votes

Please test to add Query under last range of your list range like this (validation in DestSheet or as your actual sheet you used):

=query( arrayformula( if( isnumber(DestSheet!B2:B),text(DestSheet!B2:B,"0"),DestSheet!B14:B)),"select Col1 where Col1 like '#%'")

and change your validation (Range List), for example your range for list is in SrcSheet, write this:

SrcSheet!A1:A