I have two spreadsheets (S1 and S2). I want to apply conditional formatting to column D from S1 such that: if text from cell D1, D2,... from S1 is present in column A from S2, a cell is colored green if text from cell D1, D2,... from S1 is not present in column A from S2, a cell is colored red Could not get my mind around it, could you please help? I assume I must use a formula, but not sure how...

Reputation: 1097

Google sheets - conditional formatting from other sheet

I have two spreadsheets (S1 and S2). I want to apply conditional formatting to column D from S1 such that:

if text from cell D1, D2,... from S1 is present in column A from S2, a cell is colored green
if text from cell D1, D2,... from S1 is not present in column A from S2, a cell is colored red

Could not get my mind around it, could you please help? I assume I must use a formula, but not sure how...

Upvotes: 3

Reputation: 59495

Select S1!D:D and (i) apply 'standard' red fill, (ii) Format > Conditional formatting..., Format cells if... Custom formula is and:

=match(D1,indirect("S2!A:A"),)

with green highlighting and Done.

Upvotes: 4

Reputation: 1

and to reference another sheet in conditional formatting you will need to use INDIRECT() - https://webapps.stackexchange.com/a/126045/186471

Upvotes: 0