Google Docs (Spreadsheets): Conditional Formatting

Question

Google Docs (Spreadsheet) tells me that this (custom) formula is wrong.. in Excel instead works:

=AND(MATCH(CONCATENATE($A2;" ";$B2);Allievi!$G$1:$G$1204;0); $L2="Facilitatore di classe")

It just tells me it's not valid. The problem is that it seems not possible to reference other sheets (in the same doc) in conditional formatting rules..

Any workaround? I need to keep data in different sheets.

Answer 1

You can reference the sheet and cell indirectly.

=INDIRECT("SHEETNAME!"&"CELLRANGE")

INCLUDE THE QUOTATION MARKS

For your formula it should be something like

=AND(MATCH(CONCATENATE($A2;" ";$B2);INDIRECT("Allievi!"&"$G$1:$G$1204";0); $L2="Facilitatore di classe")