Find all cells in a Google spreadsheet that failed validation

Question

I have a large Google spreadsheet in which I have defined data validation rules. When a cell fails validation, there's a warning displayed (or when using "Reject input", an error message is displayed and it won't even let me enter invalid data, but I don't want to reject input, just show a warning).

Is there a filter or any other way to only show rows that have cells with these warnings in them? Or at least find it somehow so I can keep on "finding next" to go through all invalid cells? Right now, I have to visually scan the entire spreadsheet looking for the little warning icon, and I could be missing a few if I'm scrolling too fast.

Answer 1

I would try using a combination of IFERROR formulas with VLOOKUP then you could set the errors to return a Identifier which you can use with a filter function to pull out all the ERRORS into another table

=IFERROR(VLOOKUP(E3,B$3:C$16,2,False),"N/A")  

=FILTER(E3:F16,F3:F16="N/A")  v

You can use this spreadsheet for a reference to the idea. https://docs.google.com/spreadsheets/d/1qczxeJNVlXm_04rK3FE2Sv5PgOapYWv67xOSFxxH5lg/edit?usp=sharing