Formula (not a macro) to find the last row and last column across multiple tabs

[bbbb1234]

• In a workbook, I have one main tab and anywhere from 10 to 100+ tabs.
• With help from the internet, I have defined a name, "SheetNames" and placed this formula, =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),""), in the Refers to box.
• Row 1 has column headers
• In Column A, starting at A2 I put the formula =Row()-1 to be used in Column B as a tab reference
• In Column B, starting with B2, I put the formula =@INDEX(SheetNames,A2) for as many sheets as I have in the workbook

Which looks like this:

2022-05-31 COMPARE TWO XL FILES WITH MANY SHEETS.xlsm
	A	B	C	D	E
1	Index	SheetName	Link	MAX ROW	MAX COL
2	1	TabList	TabList
3	2	Sheet0	Sheet0
4	3	Sheet1	Sheet1
5	4	Sheet2	Sheet2
6	5	Sheet3	Sheet3
7	6	Sheet4	Sheet4
8	7	Sheet5	Sheet5
9	8	Sheet6	Sheet6
TabList

Cell Formulas
Range	Formula
A2:A9	A2	=ROW()-1
B2:B9	B2	=INDEX(SheetNames,A2)
C2:C9	C2	=HYPERLINK("#'"&B2&"'!A1",B2)

What I want is a formula starting in D2 that will find the identify the last row in each sheet and starting in E2, a formula that will identify the last column in each sheet. Ideally, I do not want to use a macro to do this but rather a formula.

Any help would be greatly appreciated!!!!

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E
1	Index	SheetName	Link	Max Row	Max Col
2	1	TabList	TabList
3	2	Sheet2	Sheet2	10	6
4	3	Sheet3	Sheet3	5	2
5	4	Sheet4	Sheet4	0	0
6
TabList

Cell Formulas
Range	Formula
A2:A5	A2	=SEQUENCE(ROWS(B2#))
B2:B5	B2	=TRANSPOSE(MID(SheetNames,FIND("]",SheetNames)+1,999))
C2:C5	C2	=HYPERLINK("#'"&B2&"'!A1",B2)
D3:D5	D3	=MAX(IF(INDIRECT("'"&$B3&"'!A1:Z100")<>"",ROW($A$1:$Z$100)))
E3:E5	E3	=MAX(IF(INDIRECT("'"&B3&"'!A1:Z100")<>"",COLUMN($A$1:$Z$100)))
Dynamic array formulas.

I used a slightly different SheetNames formula:

=GET.WORKBOOK(1)

and just leveraged the dynamic functions of Excel 365, but your way works fine. The D and E formulas should work for you, but you will need to use Control+Shift+Enter if you use Excel 2016. Also depending on what else you have in your workbook, and how big you make the range in the D and E formulas, they could slow things down. If you insert or delete any rows/columns on the TabList sheet, the range can change in the formulas, so you may want to use INDIRECT around the second part of the formula too.

 

[bbbb1234]

Eric W -

Many thanks for your solution. In most cases, it worked. Here are my follow up questions.
1/ For the cased where the formula did not work, I am seeing:
- Max Row and/or Max Col=0 when there is actually text on the sheet
- #VALUE! where the last row or column has a #VALUE! in cells including the last cell in the tab
- #REF! when there are pasted pictures or jpgs on the tab
Any ideas on fixing these?

2/ Rather than a numeric value in the "Max Col" for R1C1 type of references, is there an easy way to make it alphabetic value for Max Col like an A1 type of reference?

3/ Can you explain the formula you crafted so I can better understand the various parameters you used and adapt it for when the number of tabs is greater than 100? And provide a few examples?

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G
1	Index	SheetName	Link	Max Row	Max Col		Range to Search
2	1	TabList	TabList				A1:AB250
3	2	Sheet2	Sheet2	10	M
4	3	Sheet3	Sheet3	6	D
5	4	Sheet4	Sheet4	0	#VALUE!
6
TabList

Cell Formulas
Range	Formula
A2:A5	A2	=SEQUENCE(ROWS(B2#))
B2:B5	B2	=TRANSPOSE(MID(SheetNames,FIND("]",SheetNames)+1,999))
C2:C5	C2	=HYPERLINK("#'"&B2&"'!A1",B2)
D3:D5	D3	=MAX(IFERROR(IF(INDIRECT("'"&$B3&"'!" &$G$2)<>"",ROW(INDIRECT($G$2))),ROW(INDIRECT($G$2))))
E3:E5	E3	=SUBSTITUTE(ADDRESS(1,MAX(IFERROR(IF(INDIRECT("'"&B3&"'!"&$G$2)<>"",COLUMN(INDIRECT($G$2))),COLUMN(INDIRECT($G$2)))),4),1,"")
Dynamic array formulas.

- Max Row and/or Max Col=0 when there is actually text on the sheet

I suspect you didn't enlarge the search range enough. The formula works by looking at every cell in a given range, and if it's non-empty, saves the row (or column). If there's something outside of the search range, it won't find it. I put the search range in G2 to make it easier to change.

- #VALUE! where the last row or column has a #VALUE! in cells including the last cell in the tab

The original formula would get an error if it found an error, I changed it to still get the row or column of a cell with the error.

- #REF! when there are pasted pictures or jpgs on the tab

I was not able to duplicate this problem. Excel has several ways to post a picture. It's possible the previous change fixed this too.

Rather than a numeric value in the "Max Col" for R1C1 type of references, is there an easy way to make it alphabetic value for Max Col like an A1 type of reference?

Yes, I change the E formula to return the column letter.

Can you explain the formula you crafted so I can better understand the various parameters you used and adapt it for when the number of tabs is greater than 100?

The formula uses INDIRECT to look at the sheet in column B. It then looks at each cell in the search range on that sheet. If it's non-blank, it will save the row number. After looking at each cell, the MAX finds the maximum row number. The E formula works the same way, but once it gets the maximum numeric column number, it gives it to the ADDRESS function, along with a row of 1. Then it uses SUBSTITUTE to remove the 1 from the A1 style reference.

Hope this helps!