+ Reply to Thread
Results 1 to 15 of 15

Evaluating a cell to apply specific conditional formatting to other cells

  1. #1
    Registered User
    Join Date
    12-26-2015
    Location
    Oklahoma, USA
    MS-Off Ver
    Office 365
    Posts
    4

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #3
    Registered User
    Join Date
    12-26-2015
    Location
    Oklahoma, USA
    MS-Off Ver
    Office 365
    Posts
    4

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

    Quote Originally Posted by Pete_UK View Post
    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.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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
    Attached Files Attached Files
    Last edited by Pete_UK; 12-27-2015 at 09:15 PM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

    hmm wont let me post

    Having the same problem Pete
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

    this is < that

    edit: ok so maybe that isnt the problem

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #12
    Registered User
    Join Date
    12-26-2015
    Location
    Oklahoma, USA
    MS-Off Ver
    Office 365
    Posts
    4

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

    Quote Originally Posted by Pete_UK View Post
    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. #13
    Registered User
    Join Date
    12-26-2015
    Location
    Oklahoma, USA
    MS-Off Ver
    Office 365
    Posts
    4

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

    Quote Originally Posted by FDibbins View Post
    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. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

    Quote Originally Posted by EZFoxOne View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional formatting can't apply to all cells properly
    By kelvinvu12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-16-2015, 12:05 PM
  2. [SOLVED] Need to apply conditional formatting of a cell base on text in multiple other cells
    By Herkfixer in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2013, 08:59 PM
  3. [SOLVED] Conditional Formatting to apply to two cells
    By c.drysdale89 in forum Excel General
    Replies: 2
    Last Post: 01-25-2013, 08:26 AM
  4. [SOLVED] Conditional Formatting Apply to multiple Cells
    By ganesh81289 in forum Excel General
    Replies: 4
    Last Post: 06-22-2012, 11:08 PM
  5. Replies: 4
    Last Post: 10-17-2011, 11:01 AM
  6. Replies: 3
    Last Post: 04-30-2011, 11:46 PM
  7. Replies: 4
    Last Post: 01-11-2010, 01:07 PM

Bookmarks

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