Conditional Formatting based off criteria in multiple cells (formula based cond form)

[tuckerpmission]

I am running productivity for my company and would like the productivity numbers to be conditionally formatted based on the values in the cells I am formatting, in addition to value in a second cell. Basically, we have 3 types of employees: Full Time, Part Time, and Per Visit, which would be listed in column A in this example. Their productivity numbers will be in column B.

Below is the criteria I need:
Full timers - green if corresponding productivity in column B is 5+, yellow if between 4-5, and red if below 4. Part timers green if 4+, yellow if between 3-4, and red if below 3. Per visits will be identical to the full timers for the time being, but I could see this changing so would still want a separate formula if necessary.

I created an IF formula in conditional formatting for one specific cell (i.e. =IF($A$2="FULL TIME",$B$2>5) and set format to green fill. The problem is when I pull it down throughout the spreadsheet, it will not adapt to the new cell numbers (i.e. should move to A3/B3 and so on). I also tried with removing the $ signs. This is starting to drive me nuts and I am really overthinking it.

Thx for the help!

 

[FryGirl]

You will need a few conditions, but if a few are the same (Full Time, Part Time, or Per Visit) then you can combine.

This should get you started.

Green >> =AND($A1="Full Time",$B1>=5)
Yellow >> =AND($A1="Full Time",$B1>=4)
Red >> =AND($A1="Full Time",$B1<4)

For the Full Time and Per Visit which you said can be the same for now

Conditional Formatting

• Highlight applicable range >> B1:B100
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND(OR($A1="Full Time",$A1="Per Visit"),$B1>=5)
• Format… [Number, Font, Border, Fill]
• OK >> OK

Adjust the applicable range as required

 

[tuckerpmission]

Thank you for the response FryGirl I will try that here in a second. Is there anything else I need to do to make this format apply to all cells in column B as opposed to just B1 as shown in your example? Can I pull down and it will auto apply? Thx for the hlep

 

[FryGirl]

Your choice actually in the way you apply to the length of your range, but here are a couple of options.

Highlight the applicable range (example B1:B100) as in the example posted in #2

or

Place the conditional format in B1 and then when done select the format painter on B1 and apply to the additional cells in column B

To me (preference) is seems easier just to highlight the applicable range and then enter your condition. All done in one action.

Just pay attention to the $ signs. They should only be in-front of the column reference, not the row.

$B1 >> Yes

$B$1 >> No

B$1 >> No

Relative And Absolute References In Formulas

 

[tuckerpmission]

Thank you that has been extremely helpful. For some reason when I selected the entire range first (as you suggested), it would not apply to that but only to the first one. So I updated the top cell and then double clicked to pull it down and then when it selected all of the cells that way and I went to add the next condition it applied to all of them... weird.

Another follow up question, is there a way to include all the greens in one formula? In other words, above you showed me how I could include Full time and per visit, was there a way to add the part time criteria in the same formula so I only need one condition for each color?

----


Also, I actually need to have this apply to 5 separate columns for trend purposes (in our example, productivity data would be in B, C, D, E, and F). I am tyring to pull over to those columns but I noticed it is formatting based off data in the initial column so I am creating this conditions for each column... is there an easier way?

 

[FryGirl]

Try this for the green condition which will also work across all the columns and for all three work types

=OR(AND(OR($A1="Full Time",$A1="Per Visit"),B1>=5),AND($A1="Part Time",B1>=4))

In the Applies to part of the Conditional Formatting Rules Manager, put =$B$1:$F$100

Let me know if you need help with the other conditions

Note: Notice I removed the absolute reference from B1 to allow it to cross the additional columns, but the absolute refernce is left on A because it is the requirement for all

 

[tuckerpmission]

You are a great help I appreciate it. One last follow up question. I forgot to mention that my columns, although adjacent, have hidden columns in between. The only thing I need to work out for this is what to put in the 'apply to' part. Lets say the columns are B, AB, BC, and CD. Would I put =$B:$B, $AB:$AB, $BC:$BC, $CD:$CD (?)

Thanks again!

 

[FryGirl]

Yes, that should work.

I don't place anything in the Applies to box itself; rather, in the box type an equal sing and then click on the first column, type in a comma and repeat for as many column as required.