Help with Conditional Formatting!

[elsopix]

Hi, Hope you can help!

I am trying to simplify producing Rotas at work. What I have done is create a lay out with drop down lists refering to the staff. However some staff have different conditions, breaks etc. What I would like is to have the staff formatted in colour depending on what pool they are from.

So I have 1 workbook, with 8 sheets, one for each day of the week and one with names on for a "lookup"

I have the names grouped together on the look up. So I want to format every cell on the other sheets to say "if the name in this cell is from column X (on the lookup sheet) make it blue"

I am not finding a rule that will sort it from google. I can get it done on one cell but i cant them paint it across all the cells.

I will need 7 such rules for the different categories this will need to apply to all cells.

really hope you can help - Excel 2010 Pro

thanks
Elso

 

[chrispy]

In this example the employee names are on Sheet1 in columns A-G. On the other sheets (Mon-Sun) the firs name is in cell A2.
In the conditional formatting dialog box select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$A:$A,0) Then select the desired format
Open the conditional formatting dialog box and select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$B:$B,0) Then enter the desired format
Repeat for the five addition categories.
Highlight the formatted cell and click format painter
Remember, if you have a dollar sign before the 2 the format painter will not expand the conditional formatting correctly.

Hope this helps.

 

[elsopix]

In this example the employee names are on Sheet1 in columns A-G. On the other sheets (Mon-Sun) the firs name is in cell A2.
In the conditional formatting dialog box select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$A:$A,0) Then select the desired format
Open the conditional formatting dialog box and select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$B:$B,0) Then enter the desired format
Repeat for the five addition categories.
Highlight the formatted cell and click format painter
Remember, if you have a dollar sign before the 2 the format painter will not expand the conditional formatting correctly.

Hope this helps.

Thanks for getting back to me so quickly! Super impressed! Unfortunately this does not appear to be working - heavens knows why.
1) There is a bracket missing at the end - i added that.
2) When I paint the formula (using the cross cursor on the bottom right of the cell) it does not appear to be working on the lower rows
3) on the top row it is highlighting the whole row when I select one name.

Any ideas where I may be going wrong?

All the best
Elso

 

[FDibbins]

@ Chrispy, why are you using OR in those formulas (I see no need for that)?

@ elsopix which version of excel are you using (or, if I read correctly - are you using google docs)?

To apply CF to a range (at least in excel), you can use the 1st CF window to define the range

 

[elsopix]

Hi - Excel 2010 pro. Not google Docs! can you expand on how one would define the range?
Thanks

 

[FDibbins]

These are my general instructions for using CF....
If you are familiar with CF, ignore the 1st 2 points
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter your rule formula

edit: If you alre4ady have the rule set up, go back into the CF window, and re-set the range using the "applies to" box