Grabbing the latest data from a Column based on a cell

[ashringg]

Hello,

Calling all help! I cannot figure out a formula to make this work. I am tracking my pups got their medical history and I would like it to auto-give me a expiration date based on the most recent date i entered where its checked (I added checkboxes into columns- not sure if that is a good approach)

Instead of manually changing that date per column item, can you suggest an approach to execute my idea?
I attached a picture of the sample.

dog records.xlsx
	A	B	C	D	E	F
2	Age		Date	Rabies	Other
3	6	4	10/12/2021
4	7	5	10/15/2022
5	8	6	3/23/2023
6
7
8
9
10	What	Next	Past Due
11	Rabies	Find Column to match cell from coloumn A Then grabs the cell most recent check in the colomn Finally adds the date in column C and adds 365 days	=IF(TODAY()-B11="-#","PAST DUE","")
12	Other	"	"
Sheet6

Cell Formulas
Range	Formula
A3:A8	A3	=IF($C3=""," ",YEAR($C3)-2015)
B3:B8	B3	=IF($C3=""," ",YEAR($C3)-2017)

 

[PeteWright]

Hi @ashringg

I re-designed your table to make it a bit easier to handle. Here's what I got. It should do the job.
Please let me know if this is what you were looking for and if that's a hit, please mark it as solution.

Book3.xlsx
	A	B	C	D	E
1	UPCOMING EVENTS
2	What	Next	Past Due
3	Rabies	10/15/2023	PAST DUE
4	Other	3/22/2024
5
6	HISTORY
7	Dog Mom (born 2015)	Dog Daug (born 2017)
8	Age		Date	Rabies	Other
9	6	4	10/12/2021	x	x
10	7	5	10/15/2022	x
11	8	6	3/23/2023		x
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1

Cell Formulas
Range	Formula
B3	B3	= LOOKUP("x",D9:D23,C9:C23) + 365
C3:C4	C3	= IF(B3<TODAY(),"PAST DUE","")
B4	B4	= LOOKUP("x",E9:E23,C9:C23) + 365
A9:A12	A9	= IF( $C9="","",YEAR($C9)-2015)
B9:B12	B9	= IF( $C9="","",YEAR($C9)-2017)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:C4	Cell Value	contains "PAST DUE"	text	NO

 

[ashringg]

Hi @ashringg

I re-designed your table to make it a bit easier to handle. Here's what I got. It should do the job.
Please let me know if this is what you were looking for and if that's a hit, please mark it as solution.

Book3.xlsx
	A	B	C	D	E
1	UPCOMING EVENTS
2	What	Next	Past Due
3	Rabies	10/15/2023	PAST DUE
4	Other	3/22/2024
5
6	HISTORY
7	Dog Mom (born 2015)	Dog Daug (born 2017)
8	Age		Date	Rabies	Other
9	6	4	10/12/2021	x	x
10	7	5	10/15/2022	x
11	8	6	3/23/2023		x
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1

Cell Formulas
Range	Formula
B3	B3	= LOOKUP("x",D9:D23,C9:C23) + 365
C3:C4	C3	= IF(B3<TODAY(),"PAST DUE","")
B4	B4	= LOOKUP("x",E9:E23,C9:C23) + 365
A9:A12	A9	= IF( $C9="","",YEAR($C9)-2015)
B9:B12	B9	= IF( $C9="","",YEAR($C9)-2017)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:C4	Cell Value	contains "PAST DUE"	text	NO

This is perfect. Thank you very much!

 

[ashringg]

Hi @ashringg

I re-designed your table to make it a bit easier to handle. Here's what I got. It should do the job.
Please let me know if this is what you were looking for and if that's a hit, please mark it as solution.

Book3.xlsx
	A	B	C	D	E
1	UPCOMING EVENTS
2	What	Next	Past Due
3	Rabies	10/15/2023	PAST DUE
4	Other	3/22/2024
5
6	HISTORY
7	Dog Mom (born 2015)	Dog Daug (born 2017)
8	Age		Date	Rabies	Other
9	6	4	10/12/2021	x	x
10	7	5	10/15/2022	x
11	8	6	3/23/2023		x
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1

Cell Formulas
Range	Formula
B3	B3	= LOOKUP("x",D9:D23,C9:C23) + 365
C3:C4	C3	= IF(B3<TODAY(),"PAST DUE","")
B4	B4	= LOOKUP("x",E9:E23,C9:C23) + 365
A9:A12	A9	= IF( $C9="","",YEAR($C9)-2015)
B9:B12	B9	= IF( $C9="","",YEAR($C9)-2017)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:C4	Cell Value	contains "PAST DUE"	text	NO

Curious, do you know if there is a formula that would say to find the column that matches cell A1 ("Rabies"), then LOOKUP "x" based on that?

 

[PeteWright]

Curious, do you know if there is a formula that would say to find the column that matches cell A1 ("Rabies"), then LOOKUP "x" based on that?

I'm not sure what you mean. Could you explain it or give an example?

This is perfect. Thank you very much!

Glad to hear that.