Conditional Formatting combining IF <22 then Vlookup <> to highlight under 22 who has had a birthday

[Kelli Webb]

Hello,
I would like some assistance to highlight yellow anyone under 22 who has had a birthday, since I last ran a report.
Those that should be highlighted I've highlighted yellow, as an example.
thanks & take care,
Kelli

 

[John Jairo V]

Hi, @Kelli Webb

You can use this procedure:

1. Select B2:B20 (Activecell B2)
2. Go to Start - Group style - Conditional Formatting - New Rule
3. Use a formula to determine... and put this formula: =(B2<22)*MATCH(A2,D$2:D$20,)
4. Click format, and choose yellow fill (or your favorite color).
5. Accept all dialog boxes. Done!

Check file with solution applied. Blessings!

 

[Kelli Webb]

Hi, @Kelli Webb

You can use this procedure:

1. Select B2:B20 (Activecell B2)
2. Go to Start - Group style - Conditional Formatting - New Rule
3. Use a formula to determine... and put this formula: =(B2<22)*MATCH(A2,D$2:D$20,)
4. Click format, and choose yellow fill (or your favorite color).
5. Accept all dialog boxes. Done!

Check file with solution applied. Blessings!

Hi John,
So close. This worked perfectly for my example, but when I applied it in the spreadsheet it also highlighting anyone under 22 who hasn't had a birthday i.e. 102864 Note: yes I changed cells D17:D18 to demonstrate in the attached.
Hopefully it won't be too difficult 'just for me' lol
thanks, Kelli

 

[John Jairo V]

Hi, Kelli!

I see that 102864 number of staff is under 22 and this number has a match in D column (D17 cell exactly). What do you refer about "hasn't had a birthday"?

Put an example by hand show us the result in a proper excel file. Blessings!

 

[Kelli Webb]

Hi John, Sorry for late reply - I've been away for work & personal leave.

102864 is under 21, but hasn't had a birthday since the last time I ran the report & therefore should be excluded from being highlighted until it's 19th birthday.

The purpose of this is to manage award salary increases for employees who are 21 & under. Having it highlight when the current age differs from last time will draw my attention to make sure they are on the right rate when they've had a birthday.

I hope this makes sense?

take care, Kelli

 

[Peter Bartholomew]

I used the formula

= (Current[@Age]<=21)
* COUNTIFS(
   LastMonth[Staff], Current[@Staff],
   LastMonth[Age], "<"&Current[@Age] )

within a defined name 'increment' and then used that for the conditional format.

 

[Kelli Webb]

Thank you Peter, that is working a treat!

Have a great night - Kelli

 

[Peter Bartholomew]

That is good.
I had a thought, that the (current age <= 21) is the same as (last month < 21) combined with (last month <current age)

= COUNTIFS(
  LastMonth[Staff], Current[@Staff],
  LastMonth[Age], "<"&Current[@Age],
  LastMonth[Age], "< 21" )

might be easier to read.
(I might be the only person on the planet that concerns themselves over the readability of an Excel formula as opposed to its conciseness)

 

[Kelli Webb]

That is good.
I had a thought, that the (current age <= 21) is the same as (last month < 21) combined with (last month <current age)

= COUNTIFS(
  LastMonth[Staff], Current[@Staff],
  LastMonth[Age], "<"&Current[@Age],
  LastMonth[Age], "< 21" )

might be easier to read.
(I might be the only person on the planet that concerns themselves over the readability of an Excel formula as opposed to its conciseness)

lol don't worry I totally get it, I have the same issue at times - it is always easier to read when referring back to a formula in a year or two lol

thanks again, Kelli