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. 
Conditional Formatting based on another sheet's content
google spreadsheets;conditional formatting