Comparing an entire sheet to itself for ANY duplicates

Question

I am developing a spreadsheet for managing Crewmen assignments and the Jobs they are assigned to. The Main! sheet is a grid of tables - the table headers and cell values are pulled using data validation from Labor! and Jobs!.

On Main!, I'd like a cell to be flagged if it matches any other cell within the sheet, while still maintaining the function of bringing those cell values from Labor! and Jobs! for easy entry from a fixed list. So, if A1 and D9 are populated with the same value from the dropdown, they should be flagged/highlighted/etc. to denote duplicate entries.

I have spent some time researching this and have found different solutions that apply only to one column or range of data, but not to an entire sheet (or, let's say, from A1:Z1000), and especially not for Google Sheets.

screenshot of Main sheet

Answer 1

Try this

=countif($A$1:$Z$1000,A1)>1

entered as a custom formula in conditional formatting and applied to the whole sheet (A1:Z1000).

If you want to avoid the header rows being re-formatted (because they are duplicated), you will also need to create a rule something like this first

=and(mod(row(),8)>=1,mod(row(),8)<=3)

and apply a format of 'none'.