Get list of Worksheet Names - Excel 2008

[john.e.palmer]

Is there a way to get/export a list of the worksheet names in Excel
2008?

Thanks.

 

[Bob Greenblatt]

Is there a way to get/export a list of the worksheet names in Excel
2008?

Thanks.

Yes, the exact same way that every other version of Excel has. Go to
Insert-Name-Paste and select paste list. The list of names will begin at the
active cell in the sheet.

 

[john.e.palmer]

Yes, the exact same way that every other version of Excel has. Go to
Insert-Name-Paste and select paste list. The list of names will begin at the
active cell in the sheet.

Thanks. I was so dependent on the PUP toolbar for Win Excel, that I
never discovered this. (For those of you in Win Excel, I can
thoroughly recommend it:
http://j-walk.com/ss/pup/pup6/index.htm)

 

[john.e.palmer]

Thanks.  I was so dependent on the PUP toolbar for Win Excel, that I
never discovered this.  (For those of you in Win Excel, I can
thoroughly recommend it:http://j-walk.com/ss/pup/pup6/index.htm)

Hate to be dense, but I'm missing something here. I made a new
worksheet for the list and then went to Insert/Name. My only choices
were Define and Create.

To expand on my original question, what I would like is a list of
worksheet names in the first sheet of the workbook. These worksheet
names would hyperlinked to each sheet. My first step would be to
generate the worksheet list and paste into the first worksheet,
hyperlinking to follow.

Thanks.

 

[Bob Greenblatt]

Hate to be dense, but I'm missing something here. I made a new
worksheet for the list and then went to Insert/Name. My only choices
were Define and Create.

To expand on my original question, what I would like is a list of
worksheet names in the first sheet of the workbook. These worksheet
names would hyperlinked to each sheet. My first step would be to
generate the worksheet list and paste into the first worksheet,
hyperlinking to follow.

Thanks.

I'm not sure what your question is. Or, I think you answered it your self.
Paste names will only paste names onto the active sheet. So, paste the list
to someplace out of the way, copy what you need into another place or onto
the first sheet. Then make them hyperlinks.

 

[Mike Middleton]

john.e.palmer -

Do you want

(a) a list of the names of the worksheets in a workbook, or

(b) a list of Defined Names for a worksheet?

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Dbigg]

john.e.palmer -

Do you want

(a) a list of the names of the worksheets in a workbook, or

(b) a list of Defined Names for a worksheet?

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

Mike, I'm new to this thread, but your option "(a)...names of the worksheets
in a workbook" is exactly what I'm trying to do. I'm trying to build a Table
of Contents sheet dynamically using the tab names. If I change a tab name,
I'd like the ToC sheet to change as well. Can it be (easily) done? Thanks.
Dave

 

[JE McGimpsey]

I'm trying to build a Table
of Contents sheet dynamically using the tab names. If I change a tab name,
I'd like the ToC sheet to change as well. Can it be (easily) done?

One way:

http://www.mcgimpsey.com/excel/formulae/cell_function.html

 

[Dbigg]

One way:

http://www.mcgimpsey.com/excel/formulae/cell_function.html

I don't see how, from my worksheet called "Table of Contents" that that gets
the names of all other worksheets in the current workbook. For instance, I'm
looking to build this ...
{column A }
R1} Table of Contents (current sheet - I can get that from CELL() )
R2} Project 1
R3} Project 2
Rs} ""
Rn} Project n
... where each Project is a tab or worksheet in the current book. Then, if I
change a Project's name on its tab, I'd expect this list to be updated.

Thanks for the help.
Dave

 

[JE McGimpsey]

I don't see how, from my worksheet called "Table of Contents" that that gets
the names of all other worksheets in the current workbook. For instance, I'm
looking to build this ...
{column A }
R1} Table of Contents (current sheet - I can get that from CELL() )
R2} Project 1
R3} Project 2
Rs} ""
Rn} Project n
.. where each Project is a tab or worksheet in the current book. Then, if I
change a Project's name on its tab, I'd expect this list to be updated.

Did you try using a reference to your other sheets?


=MID(CELL("filename",Project1!A1), FIND("]", CELL("filename",
Project1!A1))+ 1, 255)

 

[Dbigg via MacKB.com]

[quoted text clipped - 14 lines]

.. where each Project is a tab or worksheet in the current book. Then, if I
change a Project's name on its tab, I'd expect this list to be updated.

Did you try using a reference to your other sheets?

=MID(CELL("filename",Project1!A1), FIND("]", CELL("filename",
Project1!A1))+ 1, 255)

JE,
This requires that I type the tab's name. Thus, it is not dynamic and needs
maintenance. I was given the following solution that seems to work. The
parts preceded by "(..." are my comments after testing.

Select cell B2 (… any cell seems to work. Its just a placeholder
Then go to the menu bar select (… for 2007, Formulas->Define Name
Insert > Name > Define
Name: Sheets (… e.g. type "Sheets" or any other desired name
Refers to: =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")
(… put this formula - as is - into the Refers To: box
Click Ok
Then input formula in cell B2 and copy down.
=INDEX(Sheets,ROWS($B$2:$B2)) (… It can go anywhere, really)

Thanks for trying ....

 

[JE McGimpsey]

This requires that I type the tab's name. Thus, it is not dynamic and needs
maintenance.

Huh? Did you try it?

Given that the explicit criterion was that the displayed sheet name
change when the tab name was changed, the way I proposed works
dynamically with no "maintenance". XL automatically updates the changed
name in the displayed value.

Your method, though, is great for creating the list.

One potential downside to your method is that if you change the sheet
name, you need to "maintain" it by manually updating the cell.

However, the main reason I don't use Get.xxx commands in defined names
is that there is a bug that causes XL04 (and with limited testing, also
XL08) to crash when a worksheet referring to those names is copied
within a workbook or moved to a new workbook.