Conditional formatting with lookup formula?

[mal.b.graham]

Hello All

I have the following tabs (workbooks) within my spreadsheet, Index, Records, Reports, Charts & List

Within the Records Tab I record against each employee as they carry out the relevant training (list compiled since Sept 07, so fairly large list)
Within the Reports Tab I have 13 different reports that can be run (using pivot table and macro)
Within the Charts Tab I have 7 different charts that can be run (using pivot table and macro)
Within the List Tab this is the reference information used for drop down list Vlookup etc (all used in the records tab)

Where I have a problem is when employee's have received the inital training and then subsequent refresher training I would like to show the most recent as valid and within date or has expired. I was looking to insert a formual in Column N (Outcome) that will look at all records for that particular employee and highlight in this column those that are the most recent "Valid" (green cell) or "Expired" (red cell) or highlight (blue cell)

So basically:-
If there is three enteries for the same employee for the same training course over three different years (say 2007,2009 & 2011) it only selects the latest one (2011) and turns cell Green with text "Valid", leave other cells blank
If the employee has only received training in 2007 & 2009 then it selects the latest one (2009) and cell turns red with text "Expired" and leaves other cell blank

This would need to update as each employee receives refresher training etc

I hope I have tried to explain my problem, but if you need further information please ask............

[zbor]

can you upload example workbook rather than picture?

[NBVC]

So as long as the last year is in 2011 or later, it's valid?

Perhaps try formula:

=IF($A2="","",IF(AND($I2=MAX(IF($A$2:$A$100=$A2,$I$2:$I$100)),YEAR(MAX(IF($A$2:$A$100=$A2,$I$2:$I$100)))>=2011),"Valid",IF(YEAR(MAX(IF($A$2:$A$100=$A2,$I$2:$I$100)))<2011,"Expired","")))

adjust ranges to suit... I guessed you are looking at column I for dates.. and also only went to row 100..

Then confirm the formula with cTRL+SHIFT+ENTER not just ENTER and copy down.

You can then set conditional formatting to see if Cell Value is ="Valid", turn Green, and one for if Cell Value is ="Expired", turn Red.

[mal.b.graham]

Many thanks for your formula..... can you explain this in a little more detail as I am trying to understand the concept......

[mal.b.graham]

I have tried your formula and found that there is an issue (maybe my mistake!) only made the following change and that was to take the range a2:a1500 and also I2: I1500

on some training carried out 19 nov 07 valid 5 years expiry date is 18 nov 2012..... using your formula it comes up as expired and this clearly isnt the case. What have I done wrong?

[NBVC]

My formula assumes that 2011 is expiry year... I don't know where the # of years valid comes from.

The formula checks current row column I against all the column I entries that have same person in column A as the current row. It gets the max (latest) date and returns that... If that date is the current row date and the year is greater than or equal to 2011, then it says "Valid", if that year is before 2011 it says "Expired", else it stays blank.

How do we know the expiry for each?

[mal.b.graham]

Each course that the employee attends could have a different valid period..... ie manual handling 2 years, FLT 3 years, Co Induction 5 years etc and the date in column I is based on a vlookup referencing to the list tab.... but they also could of had two or three refresher courses from 2007 to date and this is ongoing.

[NBVC]

Maybe you could put in some sample data in relevant tabs and show (with explanation) your expected results for different cases....

[mal.b.graham]

Uploaded file with some examples

[NBVC]

Try perhaps:

=IF(OR(I2>=TODAY(),L2="Refresher"),"Valid",IF(AND(I2<TODAY(),COUNTIFS(A:A,A2,E:E,E2,L:L,"Refresher")),"","Expired"))

copied down

[mal.b.graham]

Many thanks for this formula........ works a treat...............

How do i mark as resolved?

[NBVC]

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save