Get table name on worksheet using formulas without using VBA

[sspatriots]

Happy New Year!

I have a worksheet that list all of the worksheet names in the workbook by tab order from left to right. Column A shows the actual Worksheet number and Column B returns the corresponding Worksheet name. Each worksheet has either one table on it or no table at all. I'm looking to find a way to list the corresponding Table names in column C if one does indeed exist on that corresponding Worksheet. The part below was accomplished using this link: 3 Quick Ways to Get a List of All Worksheet Names in an Excel Workbook

Any help on this would be very much appreciated. Thanks, SS

1	2024
2	2023
3	2022
4	2021
5	2020
6	2019
7	2018
8	2017
9	2016
10	2015

 

[6StringJazzer]

I think the method you are using is probably

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

which uses the old Excel 4.0 macro commands. Structured tables were introduced in Excel 2007, so there are no commands in the old macro language to address them.

I know of no way to get table names without using VBA.

 

[sspatriots]

That's exactly what I used for a named range "ListSheets" formula. As for the table names I did manage to get something together that worked. Since cells A1 and B1 were empty in each of my worksheets that contained a table and the tables started at A1 (upper left corner), I just put "=A2" in cell "A1". Then I added the following formula to cell B1 on each worksheet that contained a table: "=MID(FORMULATEXT(A1),2,FIND("[",FORMULATEXT(A1))-2)". This put the table name in cell B1 on each worksheet with a table. On my tab that listed all of the worksheet names from left to right and their corresponding table names in column C, I entered a list of sequential numbers down column A, used "=INDEX(ListSheets,A1)" for my formula down column B (which references the named range that has the formula you mentioned in your response to my original post and then for column B, I used the formula "=INDIRECT(B1&"!$B$1")" copied all the way down to the last sequential number used in column A.

It ended up looking like the following:

1	2024	Table4711
2	2023	Table47
3	2022	Table46
4	2021	Table44
5	2020	Table6
6	2019	Table7
7	2018	0​
8	2017	0​
9	2016	0​
10	2015	0​
11	2014	0​
12	2013	0​
13	2012	0​
14	2011	0​
15	2010	0​
16	2009	0​
17	2008	0​

Maybe this will help someone else down the road. Thanks, SS

 

[6StringJazzer]

I think I get what you are doing but I am confused about one thing.

cells A1 and B1 were empty in each of my worksheets that contained a table and the tables started at A1

How can a table start at A1 and also A1 be empty?

Also if I start a table in A2 and then put this formula in A1

=A2

I do not get the same result. However, if I type "=" into A1 then click on A2, I get this formula

=Table2[[#Headers],[a]]

Then if I put your formula in B1 it gives me the table name.

Here is a slightly more elegant formula for B1.

=MID(TEXTBEFORE(FORMULATEXT(A1),"["),2,99)

This is a creative solution if there is only one table per sheet and if all the tables are in the same place and if the same cell is available in every sheet for the needed formula. However, I did not know any of those things when I tried to solve your problem based on your initial post. Nicely done.

 

[Fluff]

As you are using 365 you could use this formula instead

=TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]"))

and then in the sheet you can just use

=ListSheets

which will then spill down as far as needed.

 

[sspatriots]

I think I get what you are doing but I am confused about one thing.

How can a table start at A1 and also A1 be empty?

Also if I start a table in A2 and then put this formula in A1

=A2

I do not get the same result. However, if I type "=" into A1 then click on A2, I get this formula

=Table2[[#Headers],[a]]

Then if I put your formula in B1 it gives me the table name.

Here is a slightly more elegant formula for B1.

=MID(TEXTBEFORE(FORMULATEXT(A1),"["),2,99)

This is a creative solution if there is only one table per sheet and if all the tables are in the same place and if the same cell is available in every sheet for the needed formula. However, I did not know any of those things when I tried to solve your problem based on your initial post. Nicely done.

My table started in cell A2.

 

[sspatriots]

As you are using 365 you could use this formula instead

=TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]"))

and then in the sheet you can just use

=ListSheets

which will then spill down as far as needed.

That's clever. It worked great. Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sspatriots]

Glad we could help & thanks for the feedback.

I have another issue that has popped up with this. The column populates as it is supposed to when I open the file. When other users open this same file they get "#BLOCKED!" in the first cell where the formula below is entered? Any idea what could cause this?

=ListSheets

 

[Fluff]

Make sure that they have macros enabled.