Use Cell Address in Formula

[MrDB4Excel]

How to Use the Cell Address Shown in One Cell for Another Cell's Formula
I cannot seem to get this to work. I have tried several ways and all return error or #VALUE or some such.
The attached XL2BB shows the layout of this worksheet, which is similar to 18 worksheets in this workbook, but with varying number of rows.
The reason I am trying to get this to work is when files are added to folders then the cells A1 & A2 update the end portion of the range per the cell address value shown in E3.
So for example in this worksheet there are now 8 files showing from the Power Query, consequently cell A1’s range is G5:G12 (G12 matches the row number in $H$12).
When one or more files are added to this folder and the Power Query is refreshed then that number of new files changes the cell address in E3 from $H$12 to $H$??. It is this new row number value I want to auto-update the row number in cells A1 & A2 formula’s end portion of the range G5:G??.
Does this make sense? I would very much appreciate any input on this, thank you.

I saw a mention of combining cell address and indirect, but the link to explain did not provide an answer to my specific situation. Maybe I did not research far enough, but it seemed to be a dead-end.

dodge.xlsm
	A	B	C	D	E	F	G	H	I
1	8	F:\Dwight\Memoirs\Docs\dodge\CRPS\	2z		8	TOC	1		12
2	0	dodge\CRPS\	2z	<<< Jump To This Sheet's Link In The Summary Sheet	8
3	8	dodge\CRPS	2	<<< This Sheet's Name	$H$12
4	Index	File Name	Containing Folder	Concatenated File Name	Hyperlinks	Date Modified
5	1	CRPS Design Narrative.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\CRPS Design Narrative.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\CRPS Design Narrative.docx	03/25/2020 14:45	1	1
6	2	CRPS Design Narrative.pdf	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\CRPS Design Narrative.pdf	F:\Dwight\Memoirs\Docs\dodge\CRPS\CRPS Design Narrative.pdf	03/25/2020 14:45	2	2
7	3	CRPS Developer Search.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\CRPS Developer Search.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\CRPS Developer Search.docx	03/25/2020 15:18	3	3
8	4	Development of CRPS.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\Development of CRPS.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\Development of CRPS.docx	03/10/2020 23:17	4	4
9	5	fore-and-aft rigged boat sail at DuckDuckGo.url	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\fore-and-aft rigged boat sail at DuckDuckGo.url	F:\Dwight\Memoirs\Docs\dodge\CRPS\fore-and-aft rigged boat sail at DuckDuckGo.url	12/29/2019 10:31	5	5
10	6	Lori Greiner-Shark Tank.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\Lori Greiner-Shark Tank.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\Lori Greiner-Shark Tank.docx	04/10/2020 07:39	6	6
11	7	square rigged boat sail at DuckDuckGo.url	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\square rigged boat sail at DuckDuckGo.url	F:\Dwight\Memoirs\Docs\dodge\CRPS\square rigged boat sail at DuckDuckGo.url	12/29/2019 10:30	7	7
12	8	WVDS Developer Search Pre11Mar-Changes.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\	F:\Dwight\Memoirs\Docs\dodge\CRPS\WVDS Developer Search Pre11Mar-Changes.docx	F:\Dwight\Memoirs\Docs\dodge\CRPS\WVDS Developer Search Pre11Mar-Changes.docx	03/11/2020 12:21	8	8
13
14		A1 Formula: =COUNT(G5:G12)
15		A2 Formula: =SUMPRODUCT(--NOT(ISNUMBER(G5:G12)))
16		E3 Formula: =CELL("address",INDEX(H:H,MATCH(MAX(H:H),H:H,0)))
2

Cell Formulas
Range	Formula
A1	A1	=COUNT(G5:G12)
B1	B1	=RIGHT(C5,LEN(C5)-0)
C1	C1	=LEFT(C3,1)&"z"
I1	I1	=SUMPRODUCT(MAX((H:H<>"")*ROW(H:H)))
A2	A2	=SUMPRODUCT(--NOT(ISNUMBER(G5:G12)))
B2	B2	=MID(B1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),4))+1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),5)) - FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),2))-1)
C2	C2	=C1
D2	D2	=HYPERLINK("#"&CELL("address",INDEX(Summary!$E$2:$E$30,MATCH($C$2,Summary!$E$2:$E$30,0))),"<<< Jump To This Sheet's Link In The Summary Sheet")
A3	A3	=SUM(A1:A2)
B3	B3	=LEFT(B2,LEN(B2)-1)
C3	C3	=MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)
E1	E1	=COUNT(H:H)
E2	E2	=MAX(H:H)
E3	E3	=CELL("address",INDEX(H:H,MATCH(MAX(H:H),H:H,0)))
A5:A12	A5	=VLOOKUP('2z'!$A5,'2z'!$A:$A,COLUMN('2z'!$A:$A)-COLUMN('2z'!$A:$A)+1,0)
B5:B12	B5	=VLOOKUP('2z'!$B5,'2z'!$B:$B,COLUMN('2z'!$B:$B)-COLUMN('2z'!$B:$B)+1,0)
C5:C12	C5	=VLOOKUP('2z'!$C5,'2z'!$C:$C,COLUMN('2z'!$C:$C)-COLUMN('2z'!$C:$C)+1,0)
D5:D12	D5	=CONCATENATE(C5,B5)
E5:E12	E5	=HYPERLINK(D5)
F5:F12	F5	=VLOOKUP('2z'!$D5,'2z'!$D:$D,COLUMN('2z'!$D:$D)-COLUMN('2z'!$D:$D)+1,0)
G5:G12	G5	=A5

Named Ranges
Name	Refers To	Cells
'2z'!ExternalData_1	='2z'!$A$4:$D$12	A5:A12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:F13,A17:F222,A14:A16,C14:F16	Expression	=AND($A5<>"",$G$1=ROW())	text	NO

 

[Peter_SSs]

Why not just use a large enough range in the A1 & A2 formulas to easily cover the expected number of eventual files then you never have to change those two formulas?
Might mean amending the style of formula a bit but should be doable.

For example, in A1, why not
=COUNT(G5:G1000)

 

[MrDB4Excel]

Will do, Peter. I thought of this earlier, but always trying to learn new, more difficult formulas.
Thanks
If someone else comes along with such a formula to do what I am asking about then I would try that too.
Certainly, surely, there must be a method to include one cell's cell address that resulted from that cell's address lookup and be able to include that result in another formula's range.
But then what do I know, I assure you not enough but always willing to learn new ways.

 

[Peter_SSs]

surely, there must be a method to include one cell's cell address that resulted from that cell's address lookup and be able to include that result in another formula's range.

You can - using the INDIRECT function. Something like
=COUNT(INDIRECT("H5:"&E3))
However, INDIRECT is a volatile function so generally best to avoid where other alternatives are available.

 

[MrDB4Excel]

Thanks, Peter. This will certainly be a good thing to learn regardless.