Function to Return another Worksheets Name

Is there a way I can, in the first worksheet of 40, type in a cell a function
that will return me the name of another worksheet? I know how to return the
CURRENT worksheet.

I thought I could use the Worksheets collection and the Name property, but I
am stunned and brain dead right now, and if that is a possibility, I can't
get it to work.

Any and all help is muchly appreciated.

--
Dawg House Inc.
"We live in it, therefore, we know it!"

"Dawg House Inc" wrote in message
...
Is there a way I can, in the first worksheet of 40, type in a cell a
function
that will return me the name of another worksheet? I know how to return
the
CURRENT worksheet.

I thought I could use the Worksheets collection and the Name property, but
I
am stunned and brain dead right now, and if that is a possibility, I can't
get it to work.

Any and all help is muchly appreciated.

--
Dawg House Inc.
"We live in it, therefore, we know it!"

I think this link will get you started:
http://www.cpearson.com/excel/sheetref.htm

/Fredrik

I had already been to that site, thanks. That's where I got the current
worksheet piece. I am just looking to have a refence in a "Table Of Contents"
worksheet that will grab the names of the Worksheets in my workbook. I
realize I'd have to modify each cell, but I can't figure a way of doing it
yet. Stil hunting though....

JCH

"Fredrik Wahlgren" wrote:


"Dawg House Inc" wrote in message
...
Is there a way I can, in the first worksheet of 40, type in a cell a
function
that will return me the name of another worksheet? I know how to return
the
CURRENT worksheet.

I thought I could use the Worksheets collection and the Name property, but
I
am stunned and brain dead right now, and if that is a possibility, I can't
get it to work.

Any and all help is muchly appreciated.

--
Dawg House Inc.
"We live in it, therefore, we know it!"

I think this link will get you started:
http://www.cpearson.com/excel/sheetref.htm

/Fredrik

"Dawg House Inc" wrote in message
...
Is there a way I can, in the first worksheet of 40, type in a cell a
function
that will return me the name of another worksheet? I know how to return
the
CURRENT worksheet.

I thought I could use the Worksheets collection and the Name property, but
I
am stunned and brain dead right now, and if that is a possibility, I can't
get it to work.

Any and all help is muchly appreciated.

--
Dawg House Inc.
"We live in it, therefore, we know it!"

I'm not sure I understand what you want. Let's say you have n worksheets. Do
you want a function that takes a paramter, like position, and returns the
name of the n:th worksheet?

/Fredrik

Sorry for the confusion:

I want a cell to reference a particular worksheets Name property. I then
want the cell below it to reference the Next worksheet's Name property. So,
if I have 40 sheets, I'll have 40 cells that have some sort of function in
them that would refer to the worksheets' Names in my Active Workbook.

For example: Sheet 2 - Testing; Sheet 3 - Development; Sheet 4 - Quality
Assurance

Then, on sheet 1 - Table of Contents, I would like to have cells A2:A4
show as follows:
Testing
Development
Quality Assurance


Does that clear it up? My apologies again....

JCH


"Fredrik Wahlgren" wrote:


"Dawg House Inc" wrote in message
...
Is there a way I can, in the first worksheet of 40, type in a cell a
function
that will return me the name of another worksheet? I know how to return
the
CURRENT worksheet.

I thought I could use the Worksheets collection and the Name property, but
I
am stunned and brain dead right now, and if that is a possibility, I can't
get it to work.

Any and all help is muchly appreciated.

--
Dawg House Inc.
"We live in it, therefore, we know it!"

I'm not sure I understand what you want. Let's say you have n worksheets. Do
you want a function that takes a paramter, like position, and returns the
name of the n:th worksheet?

/Fredrik

"Dawg House Inc" wrote in message
...
Sorry for the confusion:

I want a cell to reference a particular worksheets Name property. I then
want the cell below it to reference the Next worksheet's Name property.
So,
if I have 40 sheets, I'll have 40 cells that have some sort of function in
them that would refer to the worksheets' Names in my Active Workbook.

For example: Sheet 2 - Testing; Sheet 3 - Development; Sheet 4 - Quality
Assurance

Then, on sheet 1 - Table of Contents, I would like to have cells A2:A4
show as follows:
Testing
Development
Quality Assurance


Does that clear it up? My apologies again....

JCH

I think so. Start the visual Basic Editor, insert a module and paste this

Public Function GetWsName() As String
Dim row As Long

row = Application.Caller.row

If row Application.Worksheets.Count Then
GetWsName= "Out of Range"
Else
GetWsName= Application.Worksheets(row).Name
End If
End Function

If you enter =GetWsName() in A1, it will return the name of the first
worksheet
If you enter =GetWsName() in A2, it will return the name of the second
worksheet
and so on

/Fredrik

Sounds like you're building a Table of Contents?

Think Jim Cone has an add-in (XLExtras Release 117) - available upon direct
request to him - which provides excellent capabilities to easily insert /
create a Table Of Contents (including navigating to chartsheets) amongst its
many features ..

For details, try one of Jim's posts at: http://tinyurl.com/67sx2
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----
"Dawg House Inc" wrote in message
...
I had already been to that site, thanks. That's where I got the current
worksheet piece. I am just looking to have a refence in a "Table Of
Contents"
worksheet that will grab the names of the Worksheets in my workbook. I
realize I'd have to modify each cell, but I can't figure a way of doing it
yet. Stil hunting though....

JCH

Hi

Public Function TabByIndex(TabIndex As Integer) As String
' Next statement is optional
'Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function

On worksheet, enter p.e.into cell A1 the formula
=TABBYINDEX(ROW())
and copy it down. (Probably you have to wrap the formula into error check
routine too). The list of all workscheets in their actual order is
displayed.


Arvi Laanemets


"Dawg House Inc" wrote in message
...
Is there a way I can, in the first worksheet of 40, type in a cell a
function
that will return me the name of another worksheet? I know how to return
the
CURRENT worksheet.

I thought I could use the Worksheets collection and the Name property, but
I
am stunned and brain dead right now, and if that is a possibility, I can't
get it to work.

Any and all help is muchly appreciated.

--
Dawg House Inc.
"We live in it, therefore, we know it!"