google sheets data validation custom formula filter 1

google sheets data validation custom formula filter

-- Google Sheets: Using filter()
--   in custom formula in data validation

=query(filter('sheet1'!$A$2:$A, 'sheet1'$A$2:$A < 4),
       "select Col1 where Col1 = '" & 'sheet2'!A2 & "'")
       = 'sheet2'!A2

-- Note: The 'sheet2'!A2 must be
--   the first (top-left) cell of the validated range

Here is what the above code is Doing:
1. The filter() function is used to filter the data in column A of 'sheet1'
based on the value in 'sheet2'!A2.
2. The query() function is used to select the value in column A of 'sheet1'
that matches the value in 'sheet2'!A2.
3. The result of the query() function is compared to the value in 'sheet2'!A2.
If they match, the data validation is successful.

Note: The 'sheet2'!A2 must be the first (top-left) cell of the validated range.

I hope this is helpful.

Similar Posts