Search code examples
google-sheetsgoogle-sheets-formulamatchconditional-formattingtextjoin

Highlight cell if checkbox value is different between corresponding row numbers and column numbers


I wasn't even quite sure as to how to word this, but I'll give an explanation that I hope is clear.

I have a spreadsheet with color names in ColumnA2:A. In B1, I use the formula =TRANSPOSE(A2:A) to duplicate column A2:A across row 1, giving me a table with duplicate color names both in the header row and in the first column. Within this table, I have put checkboxes and deleted the checkbox where the value in Column A corresponds to the value in row 1.

Here is a screenshot of the table: Screenshot

I am pretty good with formulas, but I can't seem to figure this one out. I am needing a formula to highlight the cells where there is not a match between corresponding rows and columns. For example, if the Orange row is manually selected in the Red column, then the Red row should also be manually selected in the Orange column. My actual table is quite large and I need to figure out a quick way to find a mistake.

Here is a screenshot of what it should look like. H3 is highlighted because in the Orange column, the Violet row is selected. However, in the Violet column, the Orange row is not selected.

Screenshot2

Hope that all makes sense. Thank you.

Here is a link to the sample sheet:

Sample Data


Solution

  • try:

    =(INDEX(IFNA(MATCH($A2&B$1&B2,TOCOL($B$1:$H$1&$A$2:$A$8&$B$2:$H$8),))=""))*(B2<>"")
    

    enter image description here