Change cell format based on data on separate sheet

[SirenFeather]

I manage a database for a school program and I am trying to improve the sheet to be more helpful during our enrollment season.

Basically, our excel sheet has a 'Master Sheet' that contains the names, phone numbers, language, birthday, and a comments column that usually includes if a student tested too high to be in our program. Each semester we make a new sheet for each class. When we register a student, we type their name in and the phone #, language, and birthday aggregate on its own with a vlookup formula.

The issue is, when a student tests out we highlight their name in red on the master sheet. But unless our advisors look their name up in the master sheet, they won't know the student tested out and may register them accidently. What I want to do is when they put the student's name in the class sheet, it will do either of the following:

(a) Look for their name in the 'master sheet' and copy the formatting that cell has to the class sheet
(b) In the 'master sheet', the comments column (column e) always has an @ symbol if they tested out, though sometimes there is other information like the score and test form. So it could lookup the student's name in the 'master sheet', check if there is an @ symbol at all in column e, and change the cell color if their is

Is there a formula I can apply with conditional formatting or some other method that would make a student's cell turn red in the class sheet if they are marked as tested out in our master sheet?

*Note: We use both the desktop version and OneDrive. I usually edit the sheet in the desktop version, upload it to OneDrive, then share it with everyone so we can all can use it in real time

 

[James006]

Hi,
Much easier than copying a format ... just use the very same condition (and logic) that you have built in your "master" sheet ...

 

[SirenFeather]

I cut out a lot of the sheet, but there is a lot of data we have to record and I don't have the room to add another column. And the formatting for the main area can't be changed because we print it and it has to fit the paper. If it's all the way to the right where it clips out of the window, I doubt the advisors will check it and we'll continue to have to same issue

 

[James006]

Probably did not express myself clearly ...

You should be using the conditional formatting formula you have already implemented in your master sheet

 

[SirenFeather]

There are no conditional formatting formula's in the master sheet. The background color change was done manually for each student (though that would be pretty great to have as well lol)

 

[James006]

Let's start all over ...

Is there a formula I can apply with conditional formatting that would make a student's cell turn red if they are marked as tested out in our master sheet ?

Answer : Yes
How : with a formula
Which Formula : the formula which will encapsulate the logic you are using To Test Out

Could you precisely explain the rule you are applying in that respect ?