Conditional Formatting based on another sheet's content

Question

I am trying to set a conditional format in Google spreadsheets and I am having some difficulty. I am not sure if I should use a cell reference or if formula.

In my first Sheet called "Roster" there are 4 different groups of people, lets say "blue" "Green" "Yellow" and "Red". Now what I would like to do in the other sheets is whenever I type a person's name ex: John Doe, I would like the sheet to search the "Roster", find what group John Doe is in, and then copy the background of whatever cell John Doe is in on the "Roster" sheet.

I realize this may sound confusing so I'll try to describe it again with slightly more detail. In Sheet 1 titled "Roster", Column B is "blue", Column C is "green", Column D is "yellow, and Column F is "Red". Names in Column B fall under the "blue" group and thus have a blue background, etc.

I would like to type in "John Doe" in Sheet 2 titled "march" in A2, I want the conditional format to search the range Roster!$A$2:$F$60, Find "John Doe" (say he is in Column D "yellow group"), and color the background of March!A2 yellow.

Answer 1

Although you mention only A2 in sheet March I'm guessing you may want equivalent formatting throughout ColumnA, so start by selecting ColumnA then:

Format, Conditional formatting..., Custom formula is:

=match(A1,indirect("Roster"&"!D:D"),0)>0  

select yellow (Range: should be A:A) and Save rules.

For each colour you need a separate rule (unless one colour is acceptable by default, so standard formatting applied for that) hence for red for example + Add another rule of:

=match(A1,indirect("Roster"&"!E:E"),0)>0  

and repeat for blue (B:B in place of D:D in the first formula) and green (C:C).