Create auto updating data validation list from all worksheet names

[fryguy]

I am stumped. I would like to create a list from all the worksheet names in
a workbook. I have created these lists before by typing the names into a
list and using data validation to create the list.

Can the list be automatically updated when a new sheet is added or one is
deleted without manually changing the list each time?

 

[Gord Dibben]

Define a dynamic range and give it a name then use the name as your source in DV

=MyList

See Debra Dalgleish's site for creating and naming dynamic ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

 

[fryguy]

Thank you! I now understand the dynamic range posted on the site good to
know.

I don't understand how I would go about referencing the whortsheet names in
a workbook, without typing them into the dynamic list.

I want the list to be an auto updating list of the names of the worksheets.
How do I reference all the worksheet names?

Thanks again

 

[Gord Dibben]

I'm note sure how to get the list of sheets to update dynamically when you add
or delete sheets.

There may be an event that could be trapped but I don't know if there is a
"delete sheet" event or "add sheet" event.

You could run a macro either on workbook_open or manually to place a list of
current sheet names into column A of a sheet named "List"

This will be your dynamic range for the DVList source.

Sub ListSheets()
'list of sheet names starting at A1 on sheet "List"
Dim rng As Range
Dim i As Integer
Sheets("List").Activate
Range("A:A").ClearContents
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord

 

[fryguy]

How would I have the sheet name appear in cell A1 of each sheet? Is that
possible? Then could I use a 3D-Ref to capture all the values of the names
of the sheets and then use a dynamic list? I'm just throwing it out there
but would really know how to set it up.

thanks,

 

[Carim]

Hi,

In order to have the Sheet name appear in a cell, you can use
following :
=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)

HTH

 

[fryguy]

I managed to get the macro to work with the help of a little autoshape
button. But if anyone know how to get my other idea working please let me
know.

 

[Gord Dibben]

To have the sheetname appear in A1 of each sheet enter this in A1 of each sheet.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Group sheets so's you can enter on all at once.


Gord