Hello all, please help!
I would like to conditionally format a cell, based on the sum of values assigned to partial text fields in the same row.
confused? me too.
i have attached the sheet i am working on. basically, i want to highlight the name in red using conditional formatting, when the number of hours reached in that sun-sat row equals more than 40 hours when the fields are filled with a code. The trick is, I need to assign # of hours to correspond with the location/shift assignment in the cells. i have tried vlookup, countifs, etc, but i don't want it to be too scary looking if my boss should hover over any of the cells. i also don't want my boss to be able to delete the formula by accident (that's why the conditional formatting). I have been able to highlight if say the number of A shifts is >3, likewise with P shifts...and then >5 for D, E, and N shifts. But i can't figure out how to concisely do the same for combo shifts.
attached is the sheet i will use (the names have been substituted).
so far i have used a formula (found in Q45), but it only returns # of instances, not associated hours. the array values i placed under the total fields, B80:C84
can anyone think of anything? i have also tried sumproduct, but it won't search for wildcard/partial cell text entry.
this schedule is emailed out and i would like this formulary to be hidden.
Bookmarks