Reference Worksheet Name

[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!

[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


"Leann" wrote:

> 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!

[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

"Alex" wrote:

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

[ruby2sdy]

Leann wrote:
> 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
>
> "Alex" wrote:
>
> > 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
> >
> >
> > "Leann" wrote:
> >
> > > 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!

[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
--
AP

"ruby2sdy" <scott.steel@optusnet.com.au> a écrit dans le message de news:
1148078268.190535.219050@j73g2000cwa.googlegroups.com...
>
> Leann wrote:
>> 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
>>
>> "Alex" wrote:
>>
>> > 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
>> >
>> >
>> > "Leann" wrote:
>> >
>> > > 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!
>