Can you have a range name of a sheets that auto updates as sheets are created?

How to generate a list of sheet names using a formula:

https://www.excelforum.com/showthread.php?t=929969

Once you get the name list generated let me know where it is.

Originally Posted by Tony Valko

How to generate a list of sheet names using a formula:

https://www.excelforum.com/showthread.php?t=929969

Once you get the name list generated let me know where it is.

Thanks. Wow. I'm in deep now. Only problem is it returned just one of sheets, the first one. I was expecting them all displayed across columns(or rows?) but I just got that one first sheet. Any ideas?

Here's a small sample file created in Excel 2007 that demonstrates this.

Try adding new sheets, deleting sheets and renaming sheets and observe how the list of sheet names changes accordingly.

Originally Posted by Tony Valko

Here's a small sample file created in Excel 2007 that demonstrates this.

Try adding new sheets, deleting sheets and renaming sheets and observe how the list of sheet names changes accordingly.

Thank you. Didn't realize how it worked, that you had to use an instance of the formula for each item. Okay, got that.

Now how do I get this list to work with a drop down menu? Do I make a name for the range/list of sheet names and pop that into the "source" field of the data validation setup window? I cannot drag over the list manually since it's not in the same sheet I'm creating the drop down menu and when I click to change sheets I get an error noise(actually I probably can just manually type it in right?).

And ultimately will this sheet name list name, or however I link it to the drop down button, update from the button side? I've not spent much time on it yet but I just wanted to update and get my present ?? out. I'll report back. Thanks Tony. This is great help.

Let's assume you put the sheet names on Sheet1 starting in cell A1.

Copy the formula down to allow for future sheet additions. Let's assume you copy the formula down to A10.

Then, create this dynamic named range:

Name: SheetNameList (or whatever you want to call it)
Refers to:

=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A$1:$A$10,"?*"))

Then, as the source for your dropdown list use:

=SheetNameList

Thanks again Tony. Unfortunately "Sheet1!$A$1:$A$10" isn't updating to Sheet1!$A$1:$A$11 as I add a sheet.

1) which part of your formula is supposed to update that range as I add sheets?

2) do I need to manually add the formulas to display the sheets in each cell of my sheet list or is there some instruction in there that adds the formulas?

&

3) could we have combined this new dynamic name "sheetNamesList" with the original "sheetNames" ? Just curious if this could be smaller/cleaner.

Thanks again!

You haven't mentioned how many sheets you'll have. You need to copy the formula in A1 down to enough cells that will cover the maximum number of sheets you'll have. Is that 20? 50? 1000?

For example, right now you might have 5 sheets but you might eventually add 5 more. So, you have to copy the formula in A1 down to at least A10 to allow for the future sheet additions.

Then, simply expand the range of the dynamic range formula to ensure that it captures all the sheet names:

=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A$1:$A$1000,"?*"))

okay, thats perfect. thanks

You're welcome. Thanks for the feedback!