Sumarize the sheetnames

[anupambit1797]

Dear Experts,

                  How can I summarize the sheet names in a Particular Worksheet:-

For example:-

In below case in the sheet named 38.101-2, I want the summary of all sheet names before this Worksheet.

Thanks in Advance,

Br,

Anupam

[#2]

@anupambit1797 

In the absence of any more description,  I'll point you to the INDIRECT function.

But I'll also suggest that you provide a LOT more information, clear information, on what you're trying to do. First, I don't think you're summarizing the sheets themselves (that doesn't make sense), but rather some data from the sheets, some numbers added up, something along those lines. If whatever it is you're summarizing is in the same location in each of those sheets, that would be good to know--would help in writing an INDIRECT function to retrieve it.

Frankly, too, if all those subordinate sheets are organized identically, my longer term recommendation would be that you redesign so all the data is part of a single large database. It's easier, in Excel, to create subdivisions, summaries by category, that it is to have manually separated all of the categories and then pull them back together (as you are discovering).

[#3]

Actually I think I understand what they want and something I think Excel needs to add:
Suggestion: https://feedbackportal.microsoft.com/feedback/idea/172764e2-9c7a-ed11-a81b-000d3ae32cd0
That said, you can get sheetnames using the CELL function and a bit of a formula but that only work for locally saved excel files or using a macro (only works on desktop app) and maybe using a script.
To do it using the CELL('filename') approach you will need to have that formula on each tab and then this tab can pull all those values. A macro (or a script) will require the use of a button or some trigger to execute.
The additional need to split those tab names into 2 columns can be handled with the text functions.
Please clarify a) are you using Excel365 (if not which version)
b) does this functionality need to work online?
c) is the file saved locally?
d) are macros allowed?

[#4]

@mtarler 

Actually I think I understand what they want and something I think Excel needs to add:

Thanks for clarifying. That CELL() is a function I wasn't aware of, at least not for this kind of purpose.

[#5]

@anupambit1797 

I offer a very simple solution that may get the job done in this situation:

[#6]

https://techcommunity.microsoft.com/t5/excel/how-to-make-one-pivot-with-data-from-different-sheet/m-...

consolidate to one sheet then parse sheet name.

[#7 · Accepted solution]

Hi @anupambit1797 

Is it me or you didn't provide the complete picture? Looking at your sheetnames they ressemble to the default names when importing pages/tables from a PDF file, ex.:

If you imported from a PDF this is a none-issue. Just put together the appropriate query to summarize the Tables & Pages: