# Evaluating a cell to apply specific conditional formatting to other cells

1. ## Evaluating a cell to apply specific conditional formatting to other cells

I have a spreadsheet of technicians that I am working on that needs to evaluate a techs level before applying conditional formatting based on performance level.

For example:

I have a level 1 technician required to maintain specific performance levels. If he's at goal it's green, between two numbers is yellow and too low is red.

The catch here is if the technician is a level 2, the required performance levels will change based on that tech level.

How would I create a formula to test the technician level, then apply conditional formatting to the adjacent cells based on that tech level?

2. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

You can just set up a small table somewhere with the Levels in one column and the 3 criteria points for that level in adjacent columns, then in your CF formula you can use a VLOOKUP formula to pick out the appropriate goal points for the level that the technician is at.

Hope this helps.

Pete

3. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

Originally Posted by Pete_UK
You can just set up a small table somewhere with the Levels in one column and the 3 criteria points for that level in adjacent columns, then in your CF formula you can use a VLOOKUP formula to pick out the appropriate goal points for the level that the technician is at.

Hope this helps.

Pete
Thank you for the response.

I'm pretty new to all of this and I've been playing around with VLOOKUP. I just can't seem to grasp exactly what I'm doing. I'm going to upload a sample of my workbook and maybe you can give me an idea of what the formula should look like.

Essentially, I'm trying to create the "PPPH" sheet to change conditional formatting on the fly. I want to be able to change tech levels, from say 1 to 2 when they promote, and have the performance requirements change based on the new requirements. Also, all the metric requirements are found on the "Variables" sheet. These variables will change as we evolve as a company.

4. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

Having composed a lengthy reply and then wanting to attach the file to it, I was not allowed to and lost the description that I had typed. So, I'm just going to attach the file, and if that gets through I'll follow up with the description.

Hope this helps.

Pete

EDIT: I tried to add the description, but the firewall wouldn't let me (and I lost it all again). Essentially, I've set up a named range called Goal_table to cover your goal parameters, as you can't refer directly to another sheet in a conditional formatting formula.

Then you need to select all the cells that this conditional formatting will apply to (i.e. F5:S6 in the example file) and click on Conditional Formatting | New Rule | Use a formula... , and you will need to do this 3 times to set up a formula for the green, red, and yellow conditions. I can't post the formulae here, as I think those are causing the problems, but you can see them by clicking on Conditional Formatting | Manage Rules, then selecting the rule and clicking Edit rule.

Post back if there is anything you don't understand.

Pete

5. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

hmm wont let me post

Having the same problem Pete

6. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

1st rule....=F5 less than INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),1)

7. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

aahh its the same problem another member raised, forum for some reason does not like the greater than or less than signs

8. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

this is < that

edit: ok so maybe that isnt the problem

9. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

OK 1st rule...
=F5 > = INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),1)...format fill ORANGE
2nd rule...
=F5 > = INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),2) format fill GREEN
3rd rule...
=F5 < INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),1)...format fill RED

10. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

hmm interesting. it seems it's OK if you add a space between < and =

11. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

I just noticed this from Pete...
as you can't refer directly to another sheet in a conditional formatting formula.
No, not in 2007 and erlier, you are correct. But from 2010 onwards, you can reference other sheets directly (not sure about 365 tho)

12. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

Originally Posted by Pete_UK
Having composed a lengthy reply and then wanting to attach the file to it, I was not allowed to and lost the description that I had typed. So, I'm just going to attach the file, and if that gets through I'll follow up with the description.

Hope this helps.

Pete

EDIT: I tried to add the description, but the firewall wouldn't let me (and I lost it all again). Essentially, I've set up a named range called Goal_table to cover your goal parameters, as you can't refer directly to another sheet in a conditional formatting formula.

Then you need to select all the cells that this conditional formatting will apply to (i.e. F5:S6 in the example file) and click on Conditional Formatting | New Rule | Use a formula... , and you will need to do this 3 times to set up a formula for the green, red, and yellow conditions. I can't post the formulae here, as I think those are causing the problems, but you can see them by clicking on Conditional Formatting | Manage Rules, then selecting the rule and clicking Edit rule.

Post back if there is anything you don't understand.

Pete
Absolutely amazing, good sir! I appreciate your quick response and action.

Not only did this work perfectly, but I was able to pick it apart and apply it to the rest of the worksheet. Thank you so much!

13. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

Originally Posted by FDibbins
OK 1st rule...
=F5 > = INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),1)...format fill ORANGE
2nd rule...
=F5 > = INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),2) format fill GREEN
3rd rule...
=F5 < INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),1)...format fill RED
I have saved these formulas. Thank you for your contribution.

Unfortunately, I haven't been able to make them work. I plan on playing with them later to see what I'm doing wrong.

14. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

Thanks for the rep - glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

15. ## Re: Evaluating a cell to apply specific conditional formatting to other cells

Originally Posted by EZFoxOne
Unfortunately, I haven't been able to make them work. I plan on playing with them later to see what I'm doing wrong.
Have you set up the range properly? The range I used was as per your sample (bolded), you will need to adjust that to suite your file...

=F5 > = INDEX(Variables!\$B\$5:\$C\$8,MATCH(\$D5,Variables!\$A\$5:\$A\$8,0),1)...format fill ORANGE

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1