Get names of all worksheets in one worksheet without VBScript

[SeraphicSoul]

Hello all,

Haven't been able to search for something like this so I'm hoping you could help me out!

Basically I want to be able to get the names of all sheets in a workbook. I know how to get Excel to print the name of the sheet in a cell once the file is saved, and I know how to hide sheets to make sure that all sheets are referenced in a range. If the name of the worksheet is in the same cell across all sheets (except the ones I don't want included on the totals page), is there a way to call that range and have Excel print off all the worksheet names in different rows or columns via a fill function?

Difficulties: the user may add/delete, rename, and have any number of, worksheets in the workbook. Because these variables are unpredictable, I am having difficulty figuring out the way to do this. Also, the formula should be able to ignore the sheets which have no value in the expected cell.

And, of course, as thread title suggests, I am unable to use vbscript/macros in this sheet. Any ideas?

 

[SeraphicSoul]

Guess I can add that this is for Excel 2007/2010. Really need help for this one! Thanks!

 

[SeraphicSoul]

Bump! I've been researching into string functions but it seems CONCATENATE cannot take a range as an argument, and even if I got that to work I'd still have to split the resultant string somehow. I've seen tips for getting Excel to auto-create it on a single sheet but not when the range is across many sheets. VLOOKUP with a simple range across all sheets doesn't seem to work either, because I cannot make each sheet auto-generate a unique value when created, and it will only grab the first value in a range of identical values.

Thoughts?

 

[Michael M]

Try this, kindly provided by Richard Schollar
You will have to adjust for cell location

1. Create a new defined name caled shts and define it:

=GET.WORKBOOK(1)

and click Add

2. In a worksheet in cell A1 enter the following formula and copy down:

=INDEX(shts,ROWS($A$1:$A1))

This will give you the workbook name too (as wel as the sheet) - if you want just the sheets use:

Sheet2

 

[SeraphicSoul]

You, sir, are a scholar and a gentleman. Thank you kindly for this tip! Works like a charm. Combined with a little error catcher and a MID function, it's exactly what I'm looking for. All my google searching couldn't come up with this, so I thank you very much for this!

 

[Michael M]

Glad it worked, but Richard is both the Schollar, (pun intended) and a gentleman !!....