Sheet Names

I would like to write a formula that will put the name of the sheet on that
page.

i.e. the sheet name is "Base Case", I would like to put a function in cell
A1 that will return "Base Case". Then if I change the sheet name cell A1
will change also.

I appreciate any insights that you may have.

Wishing all of you a happy and prosperous new year!

Joe

=MID(CELL("filename",'sheet 1'!A1),FIND("]",CELL("filename",'sheet 1'!A1))+1,31)

Workbook must have been saved for this to work.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year :-)
----------------------------------------------------------------------------



"Joseph M. Yonek" wrote in message
...
I would like to write a formula that will put the name of the sheet on that
page.

i.e. the sheet name is "Base Case", I would like to put a function in cell
A1 that will return "Base Case". Then if I change the sheet name cell A1
will change also.

I appreciate any insights that you may have.

Wishing all of you a happy and prosperous new year!

Joe




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.557 / Virus Database: 349 - Release Date: 30/12/2003

You can also put this in Ths Workbook, Alt F11 then paste
the code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
Target As Range)
If Target.Address = "$A$1" Then
If Target.Value "" Then
On Error Resume Next
ActiveSheet.Name = Target.Value
On Error GoTo 0
End If
End If
End Sub

HTH&HNY
-----Original Message-----
I would like to write a formula that will put the name of
the sheet on that
page.

i.e. the sheet name is "Base Case", I would like to put
a function in cell
A1 that will return "Base Case". Then if I change the
sheet name cell A1
will change also.

I appreciate any insights that you may have.

Wishing all of you a happy and prosperous new year!

Joe


.

In A1
=MID(CELL("filename",A2),FIND("]",CELL("filename",A2))
+1,255) works as well, as the macro I posted may ignor
spaces in the tab name.
GerryK
-----Original Message-----
You can also put this in Ths Workbook, Alt F11 then paste
the code.

Private Sub Workbook_SheetChange(ByVal Sh As Object,
ByVal
Target As Range)
If Target.Address = "$A$1" Then
If Target.Value "" Then
On Error Resume Next
ActiveSheet.Name = Target.Value
On Error GoTo 0
End If
End If
End Sub

HTH&HNY
-----Original Message-----
I would like to write a formula that will put the name
of
the sheet on that
page.

i.e. the sheet name is "Base Case", I would like to put
a function in cell
A1 that will return "Base Case". Then if I change the
sheet name cell A1
will change also.

I appreciate any insights that you may have.

Wishing all of you a happy and prosperous new year!

Joe


.

.

oops!! change A2 to A1 in formula! (got my mords wixed)
-----Original Message-----
In A1
=MID(CELL("filename",A2),FIND("]",CELL("filename",A2))
+1,255) works as well, as the macro I posted may ignor
spaces in the tab name.
GerryK
-----Original Message-----
You can also put this in Ths Workbook, Alt F11 then
paste
the code.

Private Sub Workbook_SheetChange(ByVal Sh As Object,
ByVal
Target As Range)
If Target.Address = "$A$1" Then
If Target.Value "" Then
On Error Resume Next
ActiveSheet.Name = Target.Value
On Error GoTo 0
End If
End If
End Sub

HTH&HNY
-----Original Message-----
I would like to write a formula that will put the name
of
the sheet on that
page.

i.e. the sheet name is "Base Case", I would like to
put
a function in cell
A1 that will return "Base Case". Then if I change the
sheet name cell A1
will change also.

I appreciate any insights that you may have.

Wishing all of you a happy and prosperous new year!

Joe


.

.

.

Creating Formulae

The answer to this question lies in the 'CELL' worksheet formula. The
following formula gives the full path and name of the current worksheet
=CELL("filename")


This returns a value that will look something like
D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls]League Table


To retrieve just the sheet name, or even the workbook name of full path, we
need to parse the result and extract the required value.

1. The workbook path is simply
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-2)
In my example, this returns
D:\Bob\My Documents\My Spreadsheets


2. The Workbook name is
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename")
)-FIND("[",CELL("filename"))-1)
Which returns
Premiership 2003.xls


3. The sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
This final formula returns
League Table


So there we have it, 3 formulae to get the workbook path, the workbook name
and the worksheet name into a worksheet cell.

Restrictions
This technique only works for workbooks that have been saved, at least once.

One Big Problem
There is one major problem with formula 3, the worksheet name. Try this to
highlight the problem.

a.. Enter the formula in a cell on Sheet1, say A1. As expected, you will
see the value 'Sheet1' in the cell
b.. Then enter the same formula in A1 on Sheet2. Again, as expected, you
will see the value 'Sheet2' in A1
c.. Go back to Sheet1, A1 now says Sheet2

The problem here is that every time worksheet recalculation takes place,
each instance of the formula resolves itself to the active worksheet, not
the worksheet that the instance is necessarily on. Fortunately, this is
simply resolved by adding a reference to a cell, any cell, to the formula,
and this anchors the formula to the worksheet it is on. So, the worksheet
name formula then becomes
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


It matters not which cell is used, it is only a cell reference that is
needed to anchor the formula.

Making a Generic Routine
Since using the worksheet name technique, it occurred to me that it would be
useful to have it available to any workbook that I opened. This seemed easy
enough to do, just create a named range with the above formula in the new
workbook template (Book.let in the XLStart directory), using the following
steps:

a.. goto menu InsertNameDefine ...
b.. add this value to the 'Names In Workbook' input box sh.name
c.. add this formula to the 'Refers To:' input box
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Thus, every time a workbook was 'NEWed', the workbook would have a workbook
name that could be used to get the worksheet name (after it has been saved
of course), simply by putting the formula =sh.name in a cell.

Unfortunately, this doesn't quite work as it should. As before, the value on
each worksheet is always the same, even though we added the cell reference.
Unlike before, the value does not get reset every recalculation, but is
assigned the name of the worksheet that was active when the name was
defined, and only that name. This is due to the fact that when a workbook
name is defined, Excel prefixes any range reference with the name of active
worksheet. For instance, if 'Sheet1' was active when the name sh.name was
defined, the formula would finish up as
=MID(CELL("filename",Sheet1!A1),FIND("]",CELL("filename",Sheet1!A1))+1,255)
thereby negating the effect of adding the cell reference.


What we need to do is create a formula that prefixes the worksheet, but no
particular worksheet, so that we have a variable workbook name that enables
the formula to work correctly in each worksheet, but prevents Excel from
adding the active worksheet name as a prefix. Fortunately, this can be
achieved by simply adding a worksheet delimiter, namely the '!'. Thus the
formula becomes
=MID(CELL("filename",!A1),FIND("]",CELL("filename",!A1))+1,255)
Now, =sh.name returns Sheet1 on Sheet1, and Sheet2 on Sheet2.

Thus, we now have a fully flexible, domprehensive routine to return the
worksheet name of any worksheet, in any newly created workbook.

And Finally ...
In my base workbook template, I have defined a number of workbook names that
incrementally build on the basic CELL formula. I list these below, and leave
you to work them out, and use or not use as you see fit. file.full
=CELL("filename",!$A$1)
file.fullname =LEFT(file.full,FIND("]",file.full))
file.name
=MID(file.fullname,FIND("[",file.fullname)+1,LEN(file.fullname)-FIND("[",fil
e.fullname)-1)
file.dir =LEFT(file.full,FIND("[",file.full)-1)
file.sheet.name =MID(file.full,FIND("]",file.full)+1,255)


--

HTH


Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Joseph M. Yonek" wrote in message
...
I would like to write a formula that will put the name of the sheet on
that
page.

i.e. the sheet name is "Base Case", I would like to put a function in
cell
A1 that will return "Base Case". Then if I change the sheet name cell A1
will change also.

I appreciate any insights that you may have.

Wishing all of you a happy and prosperous new year!

Joe

Something simple with some limitations (recalculations needed to change the
cell).

Creat a public function in a module:

Public Function MySheetname()
MySheetName = ActiveSheet.Name
End Function

Then enter into any cell on any worksheet
=MySheetName ()



"Joseph M. Yonek" wrote in message
...
I would like to write a formula that will put the name of the sheet on
that
page.

i.e. the sheet name is "Base Case", I would like to put a function in
cell
A1 that will return "Base Case". Then if I change the sheet name cell A1
will change also.

I appreciate any insights that you may have.

Wishing all of you a happy and prosperous new year!

Joe