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