Reference Worksheet Name

L

Leann

OK, I was able to use the indirect function to help. I'm half of the way to
where I want to be.

In A1, I hard coded Sheet1 for the worksheet name. Then, in B1, I use
=INDIRECT(A1&"!b10") to get a value from Sheet1.

However, I'd like to be able to change the tab name from Sheet1 to PlantTest
and have the text value in A1 change to PlantTest automatically.

Is there a way to do this?

Thanks!
 
A

Alex

Leann

I am not aware how this can be done using Excel's built-in funcitons.

However it can be done programmatically using VBA.

(1) Open Excel and press ALT + F11. This launches VB Editor.
(2) Select <Insert><Module>
(3) In the module cut and paste:

Function GetName() As String
Application.Volatile
GetName = ActiveSheet.Name
End Function

(4) Now close VB Editor
(5) In cell A1 type

=GetName( )

(6) This will return the name of the worksheet. Each time you change the
worksheet name this will update automatically.

By the way, by adding the VBA code (as above) when you come to open the
excel workbook next time you will get a dialog box telling you that the
workbook contains a macro. Select <Enable Macros> in order for the formula
above to function.

Any problems, please write back.

Alex
 
L

Leann

Wonderful..now I need to make myself more clear. It's been a long time since
I've done programming. Here's my notes on what I'd like to do, but
definitely not in correct syntax:

Function GetName() As String
Application.Volatile
Index = 3
Do Until Index = 27
GetName = Worksheets(Index).Name
'Put the answer in the cell
'Move to the next cell down
'Add one to Index
End Function

Do you see what I'm trying to do? I don't want the ActiveSheet's name. I
want Sheets 3 through 27 names in Column A on Sheet1.

Any help is appreciated!

Thanks!
Leann
 
R

ruby2sdy

Leann said:
Wonderful..now I need to make myself more clear. It's been a long time since
I've done programming. Here's my notes on what I'd like to do, but
definitely not in correct syntax:

Function GetName() As String
Application.Volatile
Index = 3
Do Until Index = 27
GetName = Worksheets(Index).Name
'Put the answer in the cell
'Move to the next cell down
'Add one to Index
End Function

Do you see what I'm trying to do? I don't want the ActiveSheet's name. I
want Sheets 3 through 27 names in Column A on Sheet1.

Any help is appreciated!

Thanks!
Leann
Click to expand...
 
A

Ardus Petus

'------------
Function getWSname(iWS As Integer) As Variant
Application.Volatile
If iWS < 1 Or iWS > Worksheets.Count Then
getWSname = CVErr(xlErrNA)
Else
getWSname = Worksheets(iWS).name
End If
End Function
'-------------

call that function with 1 argument = worksheet no.
=getWSName(27)

HTH
 
Top