Set conditional formatting on entire row based on calculation

[wendell42]

I am trying to create a spreadsheet to track attendance

In the rows I have listed Calls, Meeting and Training by month

The columns are for each person

I would like to set the conditional formatting on each row to turn the number red if it is below the total calls, meetings or training that is held in a different sheet.

For example:

In January, Bill responded to 13 calls, Paul responded to 3 and so on (20 names total)
There were a total of 16 calls for that month.
Paul responded to less than 10% of the calls, but bill responded to more than 10%
Pauls number should be red, while Bills is black.
How can I set the formatting for the entire row? I can do it for each individual cell, but with 10 people and 12 months and 3 items per month... gunna take a LONG time.

Any help would be appreciated!

 

[TimNevins]

What is your criteria for formatting the row? 10 calls? 10% of the total number of calls? How do meeting and training figure into it?

 

[wendell42]

I have the total number of calls, meetings and training's per month listed in a master sheet. Each member is required to meet 10% of the calls and 50% of the meetings and training's each month.

I can do each cell individually for example=(K4/Numbers!B9)<0.1 (or for 50%), but it would be a LOT faster if I could do the entire row.

 

[TimNevins]

OK, here you go...

AND($B2>(0.1*TotCalls),$C2>=MeetNum,$D2>=TrainNum)

Define three names - TotCalls is the total of all calls, MeetNum is the meeting threshold, TrainNum is the training threshold.

Correct the column references to match your columns.

Click Conditional Formatting, New Rule. Select "Use a formula to determine which cells to format"

Paste the formula in with an = sign in front. Create the formatting you want in the bottom of the window and click OK.

In the Conditional Formatting Rules Manager set the Applies to value to the one you want $2:$6 (rows 2 - 6)

This is an AND relationship so all three must be true in order for the format to be applied.

Easy peasy...

Good luck!

 

[wendell42]

That's got it.

Thanks!

 

[TimNevins]

Glad to help.

Tim