Google Docs (Spreadsheets): Conditional Formatting Referencing Other Sheets

1
votes

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.

enter image description here

1
google-sheetsgs-conditional-formatting
Be careful about MATCH there, it could return NA if no match is found, and then the AND will fail. If you just want to check if something exists in the list, wrap an ISERROR around the MATCH ... - Ditto
Locale is italian. Ditto: it works on Excel though.. - MultiformeIngegno
I tried replacing ";" with ",".. still error. - MultiformeIngegno
@Ditto the MATCH comes from: stackoverflow.com/a/28676434/1342772 - MultiformeIngegno

1 Answers

2
votes

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")