Find lastest date in range

[Keebler]

SO, what I am needing is either a formula or VBA here is the criteria


there is a range of dates in column A

then there is a category (for example a MH group for Depression that I oversee/teach)


I have tried a formula

=MAXIFS($A$2:$A$101,$B$2:$B$101,$DF$2) this produces circular references
(df2 is the category name and is referenced in a vlookup formula in column A)
(I think the circular reference is coming from the range in column A including the current
row. - i'll try a ranging option that only counts rows prior to the "current" one before
posting this query)1


what I need is for when (this example) the depression group gets entered in column B that a
NEW date will be entered in column A that is a specific number of days (seven in this case)
past the most recent date. The caveat is that the depression group is NOT the only category
of entries for column B. there may be may be as many as 20 different categories each with
different date requirements. Each of the date requirements can be in a separate area of the
spreadsheet that can be referenced by either the formula(s) or VBA. the date requirements
can and usually changes as time evolves.

right now it does not matter if the date produced is in the future, but historically the
category and date are entered post event.



--
1 I added the following formula "=COUNTIF($A2:$A100,"<>")+1"
this produced the correct last line number "13"

however, when i created the dynamic range addresses

2024.xlsm
	DB	DC	DD
1	13
2	$A$13		$A$2:$A$13
3	$B$13		$B$2:$B$13
DATA

Cell Formulas
Range	Formula
DB1	DB1	=COUNTIF($A$2:$A$100,"<>")+1
DB2	DB2	=ADDRESS($DB$1,1)
DB3	DB3	=ADDRESS($DB$1,2)
DD2	DD2	="$A$2:"&$DB$2
DD3	DD3	="$B$2:"&$DB$3

using these I wrote
=MAXIFS(INDIRECT("$DD$2"),INDIRECT("$DD$3"),$DF$2)

2024.xlsm
	DG
2	1/0/1900
DATA

Cell Formulas
Range	Formula
DG2	DG2	=MAXIFS(INDIRECT("$DD$2"),INDIRECT("$DD$3"),$DF$2)

this then produced
1/0/190 which is incorrect

 

[etaf]

you have the indirect cell reference in""
should be
=MAXIFS(INDIRECT($DD$2),INDIRECT($DD$3),$DF$2)

Book3
	DB	DC	DD	DE	DF	DG	DH
1	5
2	$A$5		$A$2:$A$5		fred	6	6
3	$B$5		$B$2:$B$5
Sheet1

Cell Formulas
Range	Formula
DG2	DG2	=MAXIFS(INDIRECT($DD$2),INDIRECT($DD$3),$DF$2)
DH2	DH2	=MAXIFS(A2:A5,B2:B5,"fred")
DB2	DB2	=ADDRESS($DB$1,1)
DB3	DB3	=ADDRESS($DB$1,2)
DD2	DD2	="$A$2:"&$DB$2
DD3	DD3	="$B$2:"&$DB$3

Book3
	A	B
1
2	2	1
3	4	fred
4	5	4
5	6	fred
Sheet1

 

[Keebler]

thank you, oversight on my part
but I am still getting the circular reference error when doing the vlookup in column A and B

2024.xlsm
	A	B
14
DATA

Cell Formulas
Range	Formula
A14	A14	=IF(B14="","",IF(ISERROR(VLOOKUP(B14,$DF$2:$DH$10,3,FALSE)),"",VLOOKUP(B14,$DF$2:$DH$10,3,FALSE)))

putting anything (of the predefined options) in the search (column b) produces a circular error.

 

[etaf]

i dont see a circular ref from what you have posted

 

[Keebler]

SO, it does not matter what item is put in column b if that item is in the vlookup search field (df2:dh12) it returns a circular reference for me.
it points cell (dg2) with =MAXIFS(INDIRECT($DD$2),INDIRECT($DD$3),$DF$2) - this harvests data from column b and compares data from column a

2024.xlsm
	DB	DC	DD
1	14
2	$A$14		$A$2:$A$14
3	$B$14		$B$2:$B$14
DATA

Cell Formulas
Range	Formula
DB1	DB1	=COUNTIF($A$2:$A$100,"<>")+1
DB2	DB2	=ADDRESS($DB$1,1)
DB3	DB3	=ADDRESS($DB$1,2)
DD2	DD2	="$A$2:"&$DB$2
DD3	DD3	="$B$2:"&$DB$3

2024 .xlsm
	DG	DH
1
2	4/11/2024	4/18/2024
DATA

Cell Formulas
Range	Formula
DG2	DG2	=MAXIFS(INDIRECT($DD$2),INDIRECT($DD$3),$DF$2)
DH2	DH2	=IF(DG2="","",DG2+DK2)

and for reference

2024 .xlsm
	A
2	1/18/2024
3	2/1/2024
4	2/8/2024
5	2/15/2024
6	2/22/2024
7	2/29/2024
8	3/7/2024
9	3/14/2024
10	3/21/2024
11	3/28/2024
12	4/4/2024
13	4/11/2024
DATA

 

[Keebler]

im thinking that this can be achieved much more efficiently using vba.
i just do not know how to compile all the various data DYNAMIC data points