Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    495
    Thanks
    83
    Thanked 2 Times in 1 Post

    Help with code or Conditional Formatting

    Hi All

    Glad we are back!!

    In the attached you will see two tabs TNA & Planner.

    The planner tab holds Role names (Column I) and training options (row 6). Where E means Essential training requirement for that particular Role and D means Desirable training requirement for the role a blank cell indicates that the training requirement is not required for the Role.

    What Im trying to achieve in the TNA Tab when a role is entered in column E the cells that indicate a E or D automatically picks up training requirements and colours the cells (as in the sample). Than once a date (or a value) is entered into the a cell it turn Green. - I've tried some conditional formatting formula but can't get it to work.

    I hope that makes sense to you.

    Any assistance/thoughts would be greatly appreciated

    Regards
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,941
    Thanks
    418
    Thanked 1,593 Times in 1,445 Posts
    Verada,

    I think this is what you are after?

    TNA Sample.jpg

    Range Names: Strangely I thought I setup Roles & Skills but apparently your did and I missed it when I tried to redefine them!
    Base =Planner!$i$6
    Roles =OFFSET(Planner!$I$7,0,0,COUNTA(Planner!$I$7:$I$13 8),1)
    Skills =Planner!$J6:AC$6

    Conditional Formatting Rules: Range set to $F$5:$Z$34 on all (change as appropriate!

    E (RED) =AND(OFFSET(Base,MATCH($E5,roles,0),MATCH(F$4,Skil ls,0)) = "E",ISBLANK(F5))
    D (Lavender) =AND(OFFSET(Base,MATCH($E5,roles,0),MATCH(F$4,Skil ls,0)) = "D",ISBLANK(F5))
    Blank (Green) =NOT(ISBLANK(F5))


    Note: I had to add numbers to the three Client Place Holder items or it would always match the first one.

    Test File: TNA-RGV1.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    verada (2018-03-23)

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    495
    Thanks
    83
    Thanked 2 Times in 1 Post
    Hi RG

    That works just fine.

    I changed the name range skills to =OFFSET(Planner!$J$6,0,0,1,COUNTA(Planner!$J$6:$ZZ $6)) so will look across the row as it grows.

    Just a question with some conditional formatting - for example i have formulas in some columns and because it is not blank it is green, however I'm trying to get the cell to be clear (no color) until the formula returns a result ie "Current" or "Expired" and then it goes green.

    Thanks very much for your help



    Regards
    Last edited by verada; 2018-03-24 at 02:28. Reason: added question

Posting Permissions

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