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?

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

On Mon, 10 Dec 2007 10:59:03 -0800, fryguy
wrote:

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?

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" wrote:

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

On Mon, 10 Dec 2007 10:59:03 -0800, fryguy
wrote:

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?

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

On Tue, 11 Dec 2007 05:14:00 -0800, fryguy
wrote:

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" wrote:

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

On Mon, 10 Dec 2007 10:59:03 -0800, fryguy
wrote:

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?

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,



"Gord Dibben" wrote:

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

On Tue, 11 Dec 2007 05:14:00 -0800, fryguy
wrote:

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" wrote:

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

On Mon, 10 Dec 2007 10:59:03 -0800, fryguy
wrote:

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?

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

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.

"fryguy" wrote:

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,



"Gord Dibben" wrote:

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

On Tue, 11 Dec 2007 05:14:00 -0800, fryguy
wrote:

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" wrote:

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

On Mon, 10 Dec 2007 10:59:03 -0800, fryguy
wrote:

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?

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

On Tue, 11 Dec 2007 10:40:03 -0800, fryguy
wrote:

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,



"Gord Dibben" wrote:

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

On Tue, 11 Dec 2007 05:14:00 -0800, fryguy
wrote:

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" wrote:

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

On Mon, 10 Dec 2007 10:59:03 -0800, fryguy
wrote:

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?