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

 

[FatalLordes]

So I've worked out number 2 (and realised it should have been if greater than 365 days from today), being the following:

=AND(B1="Vax Two", $C1<TODAY()-365)

But it only highlights cell A... no biggie but would be nice to highlight the whole row.

But I don't know how to 1 where it needs to do the AND but also confirm there is no Vax Two for the same name anywhere in the table, or how to do 3 where it it need to make sure it is looking at the most recent date in the table for the name.

I'll keep playing and if I work it out I will pop it here but apreciate any guidance.

 

[myall_blues]

I believe number one should be
=AND(IF(B1="Vax One",IF(TODAY()-C1>365,TRUE,FALSE)),COUNTIFS(B$1:B$5,"=Vax Two",A$1:A$5,A1)=0)

To achieve highlighting of the whole row, select the whole area (eg A1:C5) then write your formula as if it were applying to the cell at top left.

 

[FatalLordes]

I believe number one should be
=AND(IF(B1="Vax One",IF(TODAY()-C1>365,TRUE,FALSE)),COUNTIFS(B$1:B$5,"=Vax Two",A$1:A$5,A1)=0)

To achieve highlighting of the whole row, select the whole area (eg A1:C5) then write your formula as if it were applying to the cell at top left.

Thanks myall_blues. That works. I still can't get it to highlight the whole row. Yes, I am selecting the whole area (actually selecting entire column A-C) and I'm writing as if for line 1, but it just wont. As said, not a deal breaker but would be nice.

I've actually just realised I need to include a condition to all three of the ones I'm trying to do (two which I have now and only the 3rd one to go) where it needs to check another tab in the workbook to see if the name exists and if it has a field that says "adopted" (because we don't need to highlight adopted animals). I will see if I can work it out based on your example Thank you SO much.

 

[myall_blues]

What version of Excel are you using? Knowing will affect the solution to 3.

 

[myall_blues]

If you’re using Excel 2019 or later this will work for 3.

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

To fix the row highlighting issue in you first formula change B1 to $B1. Likewise with the first formula I supplied - change A1, B1 and C1 to $A1 etc.

 

[FatalLordes]

What version of Excel are you using? Knowing will affect the solution to 3.

Excel on the Web is what I hope to keep using but worse case, I will guide people how to download and use Excel 365 on desktop.

 

[myall_blues]

My answer for #3 will work ok on web version.

 

[FatalLordes]

If you’re using Excel 2019 or later this will work for 3.

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

To fix the row highlighting issue in you first formula change B1 to $B1. Likewise with the first formula I supplied - change A1, B1 and C1 to $A1 etc.

Thank you so much. Yes this works BUT I changed the 5 to 9999 because the list of rows will always increase and it is highlighting the blank rows with nothing in them. Is there a way to say just look until there is nothing else in column A (so reach the end of the data rows)?

 

[FatalLordes]

So I'm trying to reference a sheet in the work book, but it isn't liking my formula and I figure I've done it wrong (obviously) but not sure where. So building on from what you have been guiding me, I need to add for all of the three formulas that it check the sheet called Main and the table is called Main (I assume it is table1 as its the only table on that sheet but I can't find where to confirm that) and find the same name in column A as the disregard the conditional check if the cell in column D says "Adopted" (as we don't need to follow up on adopted animals as they are no longer in our care). Following is what I had but Excel doesn't like it.

=AND(IF($D1="Vax - Two", $B1<TODAY()-365)),COUNTIFS("'Main'!Table1[Main]":$D$1:$D$9999,"Adopted",$A$1:$A$9999,$A1)=0)