Update conditional format

[hotattic]

Hi,
I am trying to work out a manning roster.On sheet1 I have a number of machines with 3 drop down lists of names for each machine. On sheet2 I have days for the next six months where holidays for each person is marked. When the name is selected from the drop down list on sheet1 the conditional format indicates if the person selected is available or not. My problem is once a name has been picked on sheet1 then IF the holiday(s) are changed the existing conditional formating on sheet1 doesn,t update. (I had tried removing the name from the source table but this left blanks in the drop down list) Any ideas, am I missing the obvious.
Regards.

 

[Gerald Higgins]

It's difficult to say, without knowing more about what you are doing.
In Excel 2003, you can't use Conditional Formating to refer to a different sheet.
Well, you can, but you have to use certain methods to do it.
So, are you using 2003, or some later version ? I think in 2007 you can possibly use CF across several sheets, but I'm not certain.
If you're using 2003, what method are you using to get round the separate sheet problem ?

 

[hotattic]

Thank you for your reply. I am using 2007 in compatibility mode and at work 2003. I am not using c/f between sheets but on the value selected from the drop down list on sheet1 pointing to a table on sheet2. ie on machine one I selected from the operator1 drop down list "fred". On sheet2 fred is on holidays and the table reflects this with a "!" placed infront of his name. The c/f is looking for the "!" and colours the cell red. If fred no longer has this day as a holiday and is edited on sheet2 sheet1 does not reflect the updated value without reselecting 'fred' from the list. If this was for a few people it would sufice but it is for five machines each with three operators and three shifts so being able to see a visual representation is important.