Function to Return another Worksheets Name

[Dawg House Inc]

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.

 

[Fredrik Wahlgren]

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.

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

/Fredrik

 

[Dawg House Inc]

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]

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.

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]

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]

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

 

[Max]

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

 

[Arvi Laanemets]

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