Conditional formatting based on the values of another sheet in Google spreadsheets
I have a Google spreadsheet with two sheets. In one of them I want to apply conditional formatting based on the values of the other sheet but when trying to apply a custom formula, Google Sheets shows me an error message stating that it is not possible to reference different sheets.
2 answers
When applying conditional formatting " formulas can only reference the same sheet by using the standard notation (='nombrehoja'!celda). To reference another sheet in the formula, use the function INDIRECT."
=INDIRECT("Hoja2!A1")>0
Reference
Coming Soon
Conditional formatting rules in Google Sheets do not allow referencing other sheets. When this is required use indirect / INDIRECT.
Explanation
IndirectUses a string as the input parameter and takes this to return the value of the reference.
Indirect has two modes, one using A1 notation and the other using r1c1 notation.
The use of r1c1 notation has the advantage that build dynamic references using numeric variables for row and column indexes.
For example, the following formula uses row() and column() to determine the row and column indexes which makes the same custom formula applicable to multiple cells specified by a single range.
=INDIRECTO("'Hoja 1'!R"&FILA()&"C"&COLUMNA(),FALSO)<>B2
