Very simply, I am attempting to highlight a cell if that cell matches (exactly) with a different cell from a different column (in a different sheet, specifically).
For example, I have an 'Active' and 'Inactive' sheet in my Google Docs Spreadsheet. Some of the items listed in 'Inactive' are also listed in 'Active', and I need to highlight those.
What I've gotten so far is this (that doesn't work):
- Format -> Conditional Formatting
- Format Cells If -> Custom Formula Is
GT(MATCH(A1, 'Active'!A2:A, 0), 0)
The general formula above works if I use it in the spreadsheet normally and correctly pass the 1st parameter to
MATCH(), however when I attempt to move the regular formula to a conditional formatting, it seems to break down: I need to pass the current cell's contents as the 1st parameter, not A1 statically. If using
MATCH() is indeed the correct way to accomplish this, I need a way to express:
GT(MATCH( 'Inactive'! [A + ROW()] , 'Active'!A2:A, 0), 0)
... Where A+ROW ends up being the non-literal expression, -- i.e. on row 123 this would refer to the cell contents of A123.
There may be also an easier and more intuitive way to do this altogether. To state my problem in a different way, "If the current cell's data [a string] is present (or 'matches') in the range
'Active'!A2:A then highlight it".