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

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

Quote from NBVC

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

Quote from NBVC

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

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

Hmmm Can't get it to work on the overview sheet that is.

Quote from NBVC

I forgot to mention that you have a trailing space in B19 of the "Overview" sheet. Remove that and you should see colour...

I've uploaded the actual sheet as it doesn't seem to be working I've applied the formatting over the range you said not sure what you meant about B19 though lol

Quote from NBVC

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)

Display More

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

Jim

Quote from james40

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