Find MAX value in multiple dynamic ranges

[wdgor]

I need to do a check to see if the user enters a date for the children, in the "Target Completion Date", that is greater than the target date of the parent ("Issue No." that is a whole number represents the parent) - the children have a decimal number in the "Issue No." field and the row is colored. The problem is that there is a variable number of potential children and those can happen with any parent, i.e. there may be no children, 1 child, 2 children, 20 children or X Children. I have a formula =COUNT(IF(MOD($4$1:$a$1000,1),A1:A1000)) that will give me a total count of the decimal entries. I can do a MAX value from that but how can I do a MAX value only looking at each parent and associated child or children? Thanks for any assistance.

 

[StephenCrump]

Here's a couple of ideas you may find useful?

	A	B	C	D	E
1	Issue No			Completion Date	MaxDate
2	1			1 May 2022	2 May 22
3	1.1			2 May 2022
4	2			13 May 2022	13 May 22
5	3			13 May 2022	13 May 22
6	4			15 May 2022	15 May 22
7	5			11 Jun 2022	15 Jun 22
8	5.1			4 Jun 2022
9	5.2			13 Jun 2022
10	5.3			4 Jun 2022
11	5.4			15 Jun 2022
12	6			20 Jun 2022	20 Jun 22
Sheet4

Cell Formulas
Range	Formula
E2:E12	E2	=IF(MOD(A2,1),"",MAX(IF(INT(A$2:A$12)=A2,D$2:D$12)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D12	Expression	=D2>VLOOKUP(INT(A2),A$2:D$12,4,)	text	NO

 

[wdgor]

Stephen - your solution works perfectly for a known populated range - thanks!!!

Here is my problem now. This sheet will continue to be expanded with users entering new lines with parents and children. If I try to extend the range in your formula beyond the last row in "A" and "D" that has a value, I get a #VALUE error. So, I tried the following:

I created a Name to get the Last Row number for Column A (LastRowA with the formula - =SUMPRODUCT(MAX(('Master Worksheet'!$A:$A<>"")*(ROW('Master Worksheet'!$A:$A)))) - this formula works on its own but slows down the sheet computation - and also one for Column P (in your example that would be col. D) - =SUMPRODUCT(MAX(('Master Worksheet'!$P:$P<>"")*(ROW('Master Worksheet'!$P:$P)))). Then I modified your formula - =IF(MOD(A4,1),"",MAX(IF(INT(A$4:LastRowA)=A4,P$4:LastRowP))) and I get a #VALUE error. I don't know if using "A$4:LastRowA" is a legitimate range designator. I tried searching around to see how I can build an expression that can be used in your formula to look at Row A and P that takes into consideration the last row with a value in each column. I tried a few things with no success. Any thoughts on that? Thanks again for your assistance.

 

[wdgor]

I realized that my formula didn't have the column designator so I tried: =IF(MOD(A4,1),"",MAX(IF(INT(A$4:A&"$"&LastRowA)=A4,P$4:P&"$"&LastRowP))).. That gave me a #NAME error. I thought it would evaluate to get the last row from LastRowA and LastRowP and concatenate that with the column, i.e. A$16 and P$16 as 16 is the last row in my test sheet. I can't figure out how to get the last row in the formula. I need an expression to get: (INT(A$4:A$last row). Driving me nuts!

 

[StephenCrump]

Perhaps:

	A	B	C	D	E
1	Issue No			Completion Date	MaxDate
2	1			1 May 2022	2 May 2022
3	1.1			2 May 2022
4	2			13 May 2022	13 May 2022
5	3			13 May 2022	13 May 2022
6	4			15 May 2022	15 May 2022
7	5			11 Jun 2022	15 Jun 2022
8	5.1			4 Jun 2022
9	5.2			13 Jun 2022
10	5.3			4 Jun 2022
11	5.4			15 Jun 2022
12	6			20 Jun 2022	20 Jun 2022
Sheet1

Cell Formulas
Range	Formula
E2:E12	E2	=IF(MOD(A2,1),"",MAX(IF(INT(IssueNo)=A2,CompletionDate)))

Named Ranges
Name	Refers To	Cells
CompletionDate	=Sheet1!$D$2:INDEX(Sheet1!$D:$D,LastRow)	E2:E12
IssueNo	=Sheet1!$A$2:INDEX(Sheet1!$A:$A,LastRow)	E2:E12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D12	Expression	=D2>INDEX(CompletionDate,MATCH(INT(A2),IssueNo))	text	NO

LastRow: =COUNTA(Sheet1!$A:$A)-COUNTA(INDEX(Sheet1!$A:$A,1):Sheet1!$A$2)+ROW(Sheet1!$A$2)

You could also use the simpler, but less robust, construction LastRow: =COUNTA(Sheet1!$A:$A)

 

[wdgor]

Thank you for your suggestion. However, when I apply it, I get a #VALUE error.

I used XL2bb to capture the sheet but it is not showing all the Named Ranges.

This is the LastRow =COUNTA('Master Worksheet'!$A:$A)-COUNTA(INDEX('Master Worksheet'!$A:$A,1):'Master Worksheet'!$A$4)+ROW('Master Worksheet'!$A$4)
This is the LastRowA1(your Issue No) ='Master Worksheet'!$A$4:INDEX('Master Worksheet'!$A:$A,LastRow)
This is the LastRowP1 (your Completion Date) ='Master Sheet'!$P$4:INDEX(Sheet1!$P:$P,LastRow)

This is the formula in Column R =SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))

Services Master List 2022_Example_dynamic_range2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	Issue No. (Do Not Modify)	Task Number	Client (Required) Select only one Client	Division (Multiple Selections Allowed)	DevOps Ticket	ZOHO Ticket	Help Desk SLA	Type (Required)	Module (Required) - Multiple Selections Allowed	Issue Date	Description	Created By	Issue Owner	Priority	Target Start Date	Target Completion Date	Maximum Target Completion Date	Column1
4	1	1CCHMC	CCHMC	All				Issue	Product						6/2/2022	6/3/2022	#VALUE!	16
5	1.1	1.1CCHMC	CCHMC	All				Issue	Product						6/2/2022	6/6/2022		16
6	2	2CCHMC	CCHMC					Bug	Root Cause Analysis								#VALUE!	16
7	3	3DIV LIRR	DIV LIRR					Feature Request	All								#VALUE!	16
8	4	4CCHMC	CCHMC						Room Hold Schedule, Add Clinic, Cancel Clinic								#VALUE!	16
9	5	5DIV LIRR	DIV LIRR												6/2/2022	6/8/2022	#VALUE!	16
10	5.1	5.1DIV LIRR	DIV LIRR												6/2/2022	7/4/2022		16
11	6	6CCHMC	CCHMC														#VALUE!	16
12	7	7MTA - JSS	MTA - JSS												6/3/2022	6/9/2022	#VALUE!	16
13	7.1	7.1MTA - JSS	MTA - JSS												6/4/2022	6/11/2022		16
14	8	8CCHMC	CCHMC	All											6/2/2022	6/8/2022	#VALUE!	16
15	8.1	8.1CCHMC	CCHMC	All											6/2/2022	6/9/2022		16
16	8.2	8.2CCHMC	CCHMC	All											6/2/2022	6/11/2022		16
17																	#VALUE!	16
18																	#VALUE!	16
19																	#VALUE!	16
20																	#VALUE!	16
21																	#VALUE!	16
Master Worksheet

Cell Formulas
Range	Formula
Q4:Q21	Q4	=IF(MOD(A4,1),"",MAX(IF(INT(LastRowA1)=A4,LastRowP1)))
R4:R21	R4	=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))
A6	A6	=IF(C6="","",IF((OR(A4="",A4="*")),A3+1,A4+1))
A7:A9,A20:A21	A7	=IF(C7="","",IF((OR(A6="",A6="*")),A4+1,A6+1))
A11	A11	=IF(C11="","",IF((OR(A9="",A9="*")),A7+1,A9+1))
A12	A12	=IF(C12="","",IF((OR(A11="",A11="*")),A8+1,A11+1))
A14	A14	=IF(C14="","",IF((OR(A12="",A12="*")),A9+1,A12+1))
B4:B21	B4	=[@[Issue No. (Do Not Modify)]]&[@[Client (Required) Select only one Client]]
A17	A17	=IF(C17="","",IF((OR(A14="",A14="*")),A11+1,A14+1))
A18	A18	=IF(C18="","",IF((OR(A17="",A17="*")),A12+1,A17+1))
A19	A19	=IF(C19="","",IF((OR(A18="",A18="*")),A14+1,A18+1))

Named Ranges
Name	Refers To	Cells
'Master Worksheet'!_FilterDatabase	='Master Worksheet'!$A$3:$AH$5000	Q14, Q6:Q9, Q11:Q12, A6
LastRowA1	='Master Worksheet'!$A$4:INDEX('Master Worksheet'!$A:$A,LastRow)	B4, Q5:Q21

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:G5000	Cell Value	="Minor"	text	NO
G4:G5000	Cell Value	="Major"	text	NO
G4:G5000	Cell Value	="Critical"	text	NO
C4:C5000	Expression	=C4=""	text	NO
I4:I5000	Expression	=AND(A4<>"",I4="")	text	NO
H4:H5,H6:I5000	Expression	=AND(A4<>"",H4="")	text	NO
E86:F3005,G86:I5000,J86:S3005,A51:D5000,E18:S85,I18:I5000,C18:D50,T17:T5000,U17:AD3005,C17:S17,A17:B50,AF4:AH3005,A5:AD16,A4:P4,S4:AD4	Expression	=$A4="*"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
N4:N21	List	=OFFSET(Setup!$K$3:$K$383,0,0,COUNTIF(Setup!$K:$K,"<>"))
C4:C5000	List	=OFFSET(Setup!$Q$3:$Q$386,0,0,COUNTIF(Setup!$Q:$Q,"<>"))
D4:D5000	List	=OFFSET(Setup!$Q$2,1,MATCH($C4,Setup!$Q$2:$X$2,0)-1,COUNTA(OFFSET(Setup!$Q$2,1,MATCH($C4,Setup!$Q$2:$X$2,0)-1,100)),1)
G4:G5000	List	=OFFSET(Setup!$AD$3:$AD$383,0,0,COUNTIF(Setup!$AD:$AD,"<>"))
H4:H5000	List	=OFFSET(Setup!$H$3:$H$383,0,0,COUNTIF(Setup!$H:$H,"<>"))
I4:I5000	List	=OFFSET(Setup!$A$2,1,MATCH($C4,Setup!$A$2:$X$2,0)-1,COUNTA(OFFSET(Setup!$A$2,1,MATCH($C4,Setup!$A$2:$G$2,0)-1,100)),1)

 

[wdgor]

OK, dumb mistake. I was relooking at the Named Ranges LastRowP1 and found a typo "Master Sheet" needs to be "Master Worksheet".

However, when I add a new row, it no longer adds increments the number in column A (I would expet a 9 in column A). That makes absolutely no sense to me as those columns were not touched with the MAX work we have been doing. I am at a loss.

Services Master List 2022_Example_dynamic_range2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	Issue No. (Do Not Modify)	Task Number	Client (Required) Select only one Client	Division (Multiple Selections Allowed)	DevOps Ticket	ZOHO Ticket	Help Desk SLA	Type (Required)	Module (Required) - Multiple Selections Allowed	Issue Date	Description	Created By	Issue Owner	Priority	Target Start Date	Target Completion Date	Maximum Target Completion Date	Column1
4	1	1CCHMC	CCHMC	All				Issue	Product						6/2/2022	6/3/2022	6/6/2022	16
5	1.1	1.1CCHMC	CCHMC	All				Issue	Product						6/2/2022	6/6/2022		16
6	2	2CCHMC	CCHMC					Bug	Root Cause Analysis								1/0/1900	16
7	3	3DIV LIRR	DIV LIRR					Feature Request	All								1/0/1900	16
8	4	4CCHMC	CCHMC						Room Hold Schedule, Add Clinic, Cancel Clinic								1/0/1900	16
9	5	5DIV LIRR	DIV LIRR												6/2/2022	6/8/2022	7/4/2022	16
10	5.1	5.1DIV LIRR	DIV LIRR												6/2/2022	7/4/2022		16
11	6	6CCHMC	CCHMC														1/0/1900	16
12	7	7MTA - JSS	MTA - JSS												6/3/2022	6/9/2022	6/11/2022	16
13	7.1	7.1MTA - JSS	MTA - JSS												6/4/2022	6/11/2022		16
14	8	8CCHMC	CCHMC	All											6/2/2022	6/8/2022	6/11/2022	16
15	8.1	8.1CCHMC	CCHMC	All											6/2/2022	6/9/2022		16
16	8.2	8.2CCHMC	CCHMC	All											6/2/2022	6/11/2022		16
17			CCHMC						Add Clinic								#VALUE!	16
18																	#VALUE!	16
19																	#VALUE!	16
20																	#VALUE!	16
21																	#VALUE!	16
Master Worksheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:G5000	Cell Value	="Minor"	text	NO
G4:G5000	Cell Value	="Major"	text	NO
G4:G5000	Cell Value	="Critical"	text	NO
C4:C5000	Expression	=C4=""	text	NO
I4:I5000	Expression	=AND(A4<>"",I4="")	text	NO
H4:H5,H6:I5000	Expression	=AND(A4<>"",H4="")	text	NO
E86:F3005,G86:I5000,J86:S3005,A51:D5000,E18:S85,I18:I5000,C18:D50,T17:T5000,U17:AD3005,C17:S17,A17:B50,AF4:AH3005,A5:AD16,A4:P4,S4:AD4	Expression	=$A4="*"	text	NO

 

[wdgor]

OK - I took a "clean" test sheet and reproduced all the Name Ranges and formulas as you suggested.

I substituted =SUMPRODUCT(MAX(('Master Worksheet'!$A:$A<>"")*(ROW('Master Worksheet'!$A:$A)))) for =COUNTA('Master Worksheet'!$A:$A)-COUNTA(INDEX('Master Worksheet'!$A:$A,1):'Master Worksheet'!$A$2)+ROW('Master Worksheet'!$A$2) as the COUNTA function didn't work (all cells were blank). I suspect that is because there are formulas in all the rows in column A and COUNTA thinks they are populated. The SUMPRODUCT command took care of that.

So, IT WORKED. However, it takes an inordinate amount of time to calculate the sheet even with one additional row added (took 77 seconds), i.e. the user wouldn't have the patience to wait for the calculation to complete and neither would I. Any thoughts on being able to speed it up?

 

[wdgor]

Stephen - I think we got it!! I changed the LastRow Named Range to =MATCH(MAX('Master Worksheet'!$A:$A)+1,'Master Worksheet'!$A:$A) and it works correctly and fast.

Thank you for all your expert assistance. I would have been scratching my head for a long time without your help.

 

[StephenCrump]

Well done for figuring it out. I'm glad we could point you in the general direction.