CODEWITHSUNDEEP
google-sheets
user14505996
user14505996

Reputation:

Conditional Formatting based on value of another sheet

I am using below formula that is working fine on same sheet to highlight the row where found "Terminated".

=$E3="Terminate"

I want to highlight the same row on other sheets as well where found "Terminate" on "Leave Data" sheet range E3.

Each sheet has same Employees name on Col B. May that would made it easy to highlight the same row on other sheets

Can someone please share a solution to this question.

Upvotes: 0

Views: 47

Answers (1)

iansedano
iansedano

Reputation: 6481

Conditional formatting does not support direct sheet references, but you can "trick" it by using INDIRECT and CELL:

So in cell A1 of Sheet3, create a conditional formatting rule valid for the whole range A1:A100 and insert this formula:

=INDIRECT("Sheet2!"&CELL("address", E1))="Terminate"

Explanation

Between INDIRECT and CELL, it creates an "indirect" reference to the value of the target cell which conditional formatting can understand.

You can also make it fill the whole row by adding a $ to the E as in this formula:

=INDIRECT("Sheet2!"&CELL("address", $E1))="Terminate"

Upvotes: 1

Related Questions