Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading