How do I conditionally format text color to match across sheets in Google Sheets?

Question

Edit: I couldn't figure out how to post pics without editing, so I added them to the OP. The first photo is the process form. On the form, as the boxes are checked the color changes from black to red to green, etc. The second is the schedule for use by field photographer to track what teams need their photo taken. I'd like for the color of on the field schedule to change as it changes in the process tracker. However, since the names of the teams will change from one event to the next, it's important that I not format the color change to correlate with specific text; rather, it should change based on any color change of text. If that makes sense.

Thank you so much for helping. I've still got some hair left that hasn't been pulled out over this. Any other questions, just let me know.

My problem is as follows:

I am creating a schedule for a sports photography company that uses several photographers to shoot team photos at tournaments. There are 2 sheets in this instance; the first is a dynamic schedule for the photographers in the field. In this sheet column C is the first team playing on a given field, D is the name of the field they are playing on, and E is the second team playing on the field.

The second sheet is a master sheet with the teams listed in column A. Columns B, C, D, and E have check boxes indicating where in the process the photos are (Photo Taken, Photo Uploaded, Proof Created, Proof Shown). As each box is checked the color of the team name changes to a different color to indicate at a glance what stage the process is in.

My question is; what formula would I use to have the color on the first sheet change in tandem with the color on the first sheet? This would allow the field photographers to know at a glance which photos have been taken and which are ready to be shown. Additionally, it is important to note that this form is meant to be reused at each event, so the team names will change as well. Is it possible to create a formula that states something along the lines of: =IF any text from sheet 2 matches text from sheet one then the color of text much also match?

Apologies for the length of this question. I just wanted to make sure that I included as much information as possible. Any other questions you have, please let me know, and MANY MANY thanks for any and all help!

Answer 1

Sorry for the delay!

What I would do personally is split this into two solutions, as far as I'm aware conditional formatting cannot access different sheets? (I may be wrong..)

I would first create a lookup column that looks for your desired column and creates a TRUE FALSE boolean: I used the following formula

=if(ISBLANK(B2),"",IF(Sheet1!B2:B = TRUE, "TRUE", "FALSE"))

Just as an FYI if a box is ticked it equates to TRUE.

Then you can apply a custom formula in your conditional formatting to highlight the row for your test. If its true it turns green.

If the above formula was in C I would use

=$C:$C="TRUE" 

in the conditional formatting.

My dummy sheet for you :

https://docs.google.com/spreadsheets/d/1kWmEF2jc_DEy5VlTDSefNEursCcTRKNpbUDKqnGcOPM/edit?usp=sharing