Conditioning Help

[FatalLordes]

Hi all

Imagine a table like the following:

Name	Desc	Date
Felix	Vax Two	01/10/2023
Gizmo	Vax One	14/03/2024
Felix	Vax One	01/09/2023
George	Vax Three	19/07/2023
George	Vax Three	19/07/2024

What I would like to do is use conditional formatting to highlight a row if one of the following conditions are met:

1. The date for a Vax One is greater than 30 days from today, and there is no Vax Two entry for the same name;
2. The date for a Vax Two is greater than 30 days from today;
3. The most recent date for Vax Three for the same name is greater than 365 days from today

I'm not really sure how to do this. Would it be three separate conditions I need to do? I think I can figure one the second condition but I'm not sure about the first and third. Any help would be greatly appreciated.

Thanks

 

[myall_blues]

Yeah that won't work.

Click in your table and look in top left of 'Table Design' tab to get the table name.

Can you post a picture of a few rows of your table?

To fix the blank row issue add another condition to check that A is not blank.

=AND($A1<>"",$C1=MAXIFS(C$1:C$5,B$1:B$5,"Vax Three",A$1:A$5,$A1),TODAY()-$C1>365)

As an aside, I usually don't work with the actual conditional formatting until I get a formula right that returns TRUE or FALSE, as conditional formatting doesn't really give any feedback while you're building it. So instead I do this for example for condition 3, and once I know it works correctly add it as a conditional format.

 

[FatalLordes]

Yeah that won't work.

Click in your table and look in top left of 'Table Design' tab to get the table name.

View attachment 114295


Can you post a picture of a few rows of your table?

To fix the blank row issue add another condition to check that A is not blank.

=AND($A1<>"",$C1=MAXIFS(C$1:C$5,B$1:B$5,"Vax Three",A$1:A$5,$A1),TODAY()-$C1>365)

As an aside, I usually don't work with the actual conditional formatting until I get a formula right that returns TRUE or FALSE, as conditional formatting doesn't really give any feedback while you're building it. So instead I do this for example for condition 3, and once I know it works correctly add it as a conditional format.
View attachment 114296

Firstly, thank you for telling me how you work out your formulas. I figured there must be a way to write them smartly, where it prompts, rather than just typing text in the conditional screen because, yeah, it doesn't give any feedback when building so if you do it wrong (as I will as I'm learning), I'm kinda stuffed So thank you for sharing that trick!

Secondly, I'll nut out that condition to say make sure A not blank. Thanks for the pointer yet again! :D

Lastly, here is my Main table on my Main tab. I really appreciate your taking the time to guide me. I'm picking your code apart to work out how and why it is working and I'm kinda getting the idea which is why I'm keen to nut out the extra condition to stop it looking at blank rows.

 

[myall_blues]

If I'd known these were cats I probably wouldn't have helped.

Formula for condition 1, excluding adopted animals and incorporating solution for blank rows (column A blank).

=AND($A1<>"",$B1="Vax Two", $C1<TODAY()-30,XLOOKUP($A1,Main[Name],Main[Status],FALSE,0)<>"Adopted")

Formula for condition 3, incorporating solution for blank rows (column A blank).

=AND($A1<>"",$C1=MAXIFS(C$1:C$5,B$1:B$5,"Vax Three",A$1:A$5,$A1),TODAY()-$C1>365)

 

[FatalLordes]

If I'd known these were cats I probably wouldn't have helped.

Formula for condition 1, excluding adopted animals and incorporating solution for blank rows (column A blank).

=AND($A1<>"",$B1="Vax Two", $C1<TODAY()-30,XLOOKUP($A1,Main[Name],Main[Status],FALSE,0)<>"Adopted")

Formula for condition 3, incorporating solution for blank rows (column A blank).

=AND($A1<>"",$C1=MAXIFS(C$1:C$5,B$1:B$5,"Vax Three",A$1:A$5,$A1),TODAY()-$C1>365)

Ahh, I came up with the following for the blank lines... and I worked out how to say search the whole column rather than me defining 9999:

=AND($C1=MAXIFS(C$:C$,B$:B$,"Vax Three",A$:A$,$A1),TODAY()-$C1>365,$A1<>"")

Seems to work.

Trying for the life of me how to say do all that and then also look at the Main sheet to find the same cat and if Cell D <> "Adopted", then highlight the row, but it is doing my head in

And, come on, this is for rescue cats... surely any rescue animal is worth helping

 

[myall_blues]

Trying for the life of me how to say do all that and then also look at the Main sheet to find the same cat and if Cell D <> "Adopted", then highlight the row, but it is doing my head in

Have a look at my last answer formula for Condition 1 - it includes excluding cats already adopted.

And, come on, this is for rescue cats... surely any rescue animal is worth helping

Of course. All our dogs except the two whippets we have now have been rescues.

 

[FatalLordes]

If I'd known these were cats I probably wouldn't have helped.

Formula for condition 1, excluding adopted animals and incorporating solution for blank rows (column A blank).

=AND($A1<>"",$B1="Vax Two", $C1<TODAY()-30,XLOOKUP($A1,Main[Name],Main[Status],FALSE,0)<>"Adopted")

Formula for condition 3, incorporating solution for blank rows (column A blank).

=AND($A1<>"",$C1=MAXIFS(C$1:C$5,B$1:B$5,"Vax Three",A$1:A$5,$A1),TODAY()-$C1>365)

Interestingly, when I try to apply your formula for the condition 1, it won't let me. It errors with the following, so I wonder if that is because it is Excel Online. It will let me put it in a cell to test like you showed me but it wont let me add to conditional formatting.

 

[FatalLordes]

Interestingly, when I try to apply your formula for the condition 1, it won't let me. It errors with the following, so I wonder if that is because it is Excel Online. It will let me put it in a cell to test like you showed me but it wont let me add to conditional formatting.
View attachment 114314

Even excel desktop doesn't like the formula when I put it into conditional formatting but it will accept it in a cell??? I've never encountered this before.

 

[Fluff]

You cannot use structured references in conditional formatting, you need to use ranges.

 

[myall_blues]

OK that's easily fixed then. Here is a new formula for condition 1.

=AND($A1<>"",$B1="Vax Two", $C1<TODAY()-365,XLOOKUP($A1,Main!$A:$A,Main!$D:$D,FALSE,0)<>"Adopted")

 

[FatalLordes]

Wanting to do a big shout out to everyone who responded to this chat to guide me but particularly to @myall_blues for guidance and explanations and showing me some tricks in how to get my coding right. With your help I now have a shared database running on Excel On The Web in OneDrive working for a charity rescue group. And I learnt a LOT! Truly appreciate it very very much!

For everyone playing at home, there are the three conditions in their entirety based on the guidance received here from others (note the cells have changed a bit but you will get the idea:

If Vax1 date is more than 30 days old and there is no Vax2 and the Status of kitten on Main table is not adopted or trial period, highlight.

=AND(IF($D1="Vax - One",IF(TODAY()-$B1>30,TRUE,FALSE)),COUNTIFS($D:$D,"Vax - Two",$A:$A,$A1)=0,XLOOKUP($A1,Main!$A:$A,Main!$D:$D,FALSE,0)<>"Adopted",XLOOKUP($A1,Main!$A:$A,Main!$D:$D,FALSE,0)<>"Trial Period")
--
If Vax2 date is more than 365 days old and there is no VaxYearly and the Status of kitten is not adopted or trial period, highlight.

=AND(IF($D1="Vax - Two",IF(TODAY()-$B1>365,TRUE,FALSE)),COUNTIFS($D:$D,"Vax - Yearly",$A:$A,$A1)=0,XLOOKUP($A1,Main!$A:$A,Main!$D:$D,FALSE,0)<>"Adopted",XLOOKUP($A1,Main!$A:$A,Main!$D:$D,FALSE,0)<>"Trial Period")
--
If most recent VaxYearly is more than 365 days old than today (and Cell A is not empty) and the Status of kitten is not adopted or trial period, highlight.

=AND($B1=MAXIFS($B:$B,$D:$D,"Vax - Yearly",$A:$A,$A1),TODAY()-$B1>365,$A1<>"",XLOOKUP($A1,Main!$A:$A,Main!$D:$D,FALSE,0)<>"Adopted",XLOOKUP($A1,Main!$A:$A,Main!$D:$D,FALSE,0)<>"Trial Period")