Data validation change cell colour based on dates in a range work sheet included

  • Ok So I'm not even sure if this is in the right forum


    but If you take a look at the sheet you will see what I'm trying to do, I have conditional formatting in both the "office based competency" sheet and " track based competency" sheet where the dates of competence expiry change the cell colour based on how far away the date is from Today( ) etc. Joe Bloggs is the example name I have left in.


    What I would like to do is change the cell colour containing Joe Bloggs name, to either White All competencies in date, yellow all in date but some within 60 days of Today () or Red Some due to expire within 30days though some maybe in date or within 60 days ie red is the final colour.


    I would like to do this on both the Office and track competence sheet and I assume the same formula / conditional formatting would apply over the ranges E26:AF26 for the office sheet and then E27:AM27 Track based Competency sheet.


    But the main thing is to give a view of these impending renewals on the Overview sheet. where Joe Bloggs cell colour would remain white or either fill red or yellow again either based on the previous ranges or perhaps using Vlookup to check the cell colours on the other 2 sheets Cells A26 and A27..


    Sorry for the long winder post I've hit a wall applying it over a range and comparative range lol


    Any help appreciated


    James

  • Perhaps you can try using COUNTIF() to count if there any number of dates less that 30+ days or 60+ days.


    e.g. for Red: =COUNTIF(E3:AF3,"<="&TODAY()+30)
    for Yellow: =COUNTIFS(E3:AF3,"<="&TODAY()+60)


    making sure to use the "Stop if True" feature in the rules and order them RED, YELLOW, WHITE

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • NBVC thanks again !


    Question can I apply this over both sheets to the overview sheet for Mr Bloggs also ??


    I'm on the train home but will try it when I get back lol


    Thanks man !!


    Jim

  • There are 2 sheets with competencies on and one over view sheet I left Joe Bloggs on all three sheets as the example and also data protection etc..
    could the overview sheet cells with the same names in, review the dates on the other 2 sheets for the range of dates for the person with the same name ?? IE the range from the 2 sheets other sheets so by looking at the overview sheet you could identify a person who's competencies are about to expire then check the 2 other sheets to identify the competencies ??


    Could I acheive that with vlookup maybe ??


    Hope that makes more sense ??


    Jim

  • Perhaps a formula like:


    =COUNTIF($E3:$AF3,"<="&TODAY()+30)+COUNTIFS(INDEX('Track based competency'!$E:$AF,MATCH(A3,'Track based competency'!$A:$A,0),),"<="&TODAY()+30)


    applied to column A starting at A3.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Perhaps you can try using COUNTIF() to count if there any number of dates less that 30+ days or 60+ days.


    e.g. for Red: =COUNTIF(E3:AF3,"<="&TODAY()+30)
    for Yellow: =COUNTIFS(E3:AF3,"<="&TODAY()+60)


    making sure to use the "Stop if True" feature in the rules and order them RED, YELLOW, WHITE


    bet there is no way to apply tat formatting over all the cells with names in ?? would have to do each cell at a time ??


    Jim

  • Perhaps a formula like:


    =COUNTIF($E3:$AF3,"<="&TODAY()+30)+COUNTIFS(INDEX('Track based competency'!$E:$AF,MATCH(A3,'Track based competency'!$A:$A,0),),"<="&TODAY()+30)


    applied to column A starting at A3.


    I don't think this will work as the names in the overview sheet are in column B where as in the track and office competence sheets they are in column A the names also occupy different rows in the sheets hence why I thought Vlookup may work ? just need to format the name cells base on the format in the other 2 sheets with red / yellow / white depending on the timescales ??


    I don't think I explained properly lol


    Jim

  • Ok, in the Overview sheet, select B4:B59 and invoke Conditional Formatting >> New Rule...


    Select "Use a formula to determine which cells to format"


    Enter formula: =COUNTIFS(INDEX('Track based competency'!$E:$AF,MATCH(B4,'Track based competency'!$A:$A,0),),"<="&TODAY()+30)+COUNTIFS(INDEX('Office based competency'!$E:$AF,MATCH(B4,'Office based competency'!$A:$A,0),),"<="&TODAY()+30)


    Then click format and choose a colour....


    If this works, you can apply similar formulas for other checks.


    Note: The INDEX/MATCH combo is like using VLOOKUP... to find match for B4 in column A of other sheets.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Cheers ears !! Could I use that formula on the other 2 sheets and modify it only to work for that sheet ? Save adding red yellow and white rules per name ??


    I'm a pain I know lol


    Jim

  • Yes, you just need to adjust the ranges and sheet references to suit...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Hi,


    Firstly, the names in column B of the "Overview" sheet do not exactly match those in column A of the other 2 sheets. You have first initial and last name in the Overview sheet, but you have full names in the other 2 sheets. That won't cut it.


    Secondly, many of the names in column B of the Overview sheet have a space at the end. If you click, for instance, on B5 of the Overview sheet (A Graham). Then click in the formula bar, you will see that there is a space after the name... that has to be removed)


    Thirdly, if you look at the formula in your conditional format rule, you will see that you've encased the formula in double quotes. That should not be. Copy this formula exactly into your conditional format rule.


    =COUNTIFS(INDEX('Track based competency'!$E:$AF,MATCH(B4,'Track based competency'!$A:$A,0),),"<="&TODAY()+30)+COUNTIFS(INDEX('Office based competency'!$E:$AF,MATCH(B4,'Office based competency'!$A:$A,0),),"<="&TODAY()+30)

    Where there is a will there are many ways. Finding one that works for you is the challenge!


  • NBVC as always thanks I'm gonna give it a try now !


    Jim

  • NBVC as always thanks I'm gonna give it a try now !


    Jim


    NBVC ! Working perfectly thanks man !!


    Do you do paid work in this area ? As the delivery dept uses quite a complex tool that is now playing up, it generates all the work packs for the coming weeks and rosters etc !


    I could enquire with them if you liked ??


    Jim

  • We can play it by year.... right now i am quite busy at work and wouldn't have time for big projects. We do have a hire help forum where you can pay for services for individual projects. You can use that if needed... or you can continue with one specific question at a time in the regular Excel questions forum.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!