Change cell color if state is complete/in progress/not started

Question

I need to format the store number depending on the state where it's located. another tab shows the state status. Green for Complete/ Orange for In Progress and Yellow for Not Started. I would appreciated it if you would show me how to do it since I have a lot of files that needs to be setup that way as well. Thanks!

Here's the google sheet link: https://docs.google.com/spreadsheets/d/1gw9PEkrMxOVbOObwCjwtkR3x0ATpJCfUIc4nhAFfj1g/edit?usp=sharing

Answer 1

You may try:

Conditional Formatting Rules

Apply to range: A2:B

Custom Formula is: =vlookup($B2,indirect("'State status'!A:B"),2,)="Complete"

Answer 2

I added a copy of "store" page to your linked sheet and added the conditional formatting. What I suggest is using VLOOKUP formula

=VLOOKUP(B2, 'State status'!A:B, 2, FALSE)

to add status from the "status" page, and use "custom formula" in conditional formatting to change the color of the store number depending on this status, e.g.:

=C2:C="Complete",

where "C" is the added "Status" column.

Hope this helps!