Conditional formatting

[icissel1234]

I am making a schedule in excel and want to highlight cells in which people are on overtime. One work sheet is a schedule a separate worksheet calculates everyones hours in a month (column a are the names and column is the hours). So in the schedule worksheet i have a conditional format that apples to the entire sheet

=and('hours'!>40,vlookup(>40,'hours'!a1:a30,1,0))

i cant understand why #1 that formula will not be accepted and #2 how to highlight the cells that correspond with shifts in which someone is on overtime

any help is greatly appreciated

 

[bbott]

Conditional formatting formulas cannot reference other sheets. You would need to have the data on the same sheet if you wanted to use CF.

 

[icissel1234]

ok thanks that solves that. is there a way to highlight just the cells that are considered overtime as opposed to every cell on the schedule where that persons name appears

 

[bbott]

Possibly. Are you able to post your table?

And so I understand...For example, a person is scheduled M-Th for 10 hrs each day (40 hrs for the week). You then want the Friday hours highlighted (over 40 for the week)? Or the name highlighted?

 

[icissel1234]

the name on the sched

 

[bbott]

Assuming names in column A and the hours for the week in columns B through H, this will work as the CF formula in A1:

=SUM(B1:H1) > 40

Does that get you going in the right direction?