Sheet Names

[Marcus]

Hello,
Is there a way yo can put a formula in a cell to have it equal what the
sheet name is? Example( I have a spreadsheet with about 30 sheets in it. Each
sheet has a different name. Instead of haveing to name every sheet, and tab
twice is there a way I can rename the tab and have a formula i the sheet that
will name the sheet the same as the tab name?)

 

[Barb Reinhardt]

Try this:
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-SEARCH("]",CELL("filename",A2)))

Just don't put it in A2 or you'll get a circular reference.

 

[Bob Phillips]

You need VBA to name a sheet after a cell, like this

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = Target.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ignore that, I thought you were asking for code to rename the sheet to the
value in the cell.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Marcus]

This worked Barb,
Thank you.

Try this:
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-SEARCH("]",CELL("filename",A2)))

Just don't put it in A2 or you'll get a circular reference.

Hello,
Is there a way yo can put a formula in a cell to have it equal what
the
sheet name is? Example( I have a spreadsheet with about 30 sheets in it.
Each
sheet has a different name. Instead of haveing to name every sheet, and
tab
twice is there a way I can rename the tab and have a formula i the sheet
that
will name the sheet the same as the tab name?)

 

[David McRitchie]

Hi Barb,
Good to be on the lookout for circular references, but ...

Actually you won't get a circular reference, because it is not picking
up the value of A2. All that A2 in your reference is going to tell Excel
is the location you are interested in -- worksheet, workbook, pathname etc.
You could use any cell on that sheet and get the same answer.
http://www.mvps.org/dmcritchie/excel/pathname.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Try this:
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-SEARCH("]",CELL("filename",A2)))

Just don't put it in A2 or you'll get a circular reference.

Hello,
Is there a way yo can put a formula in a cell to have it equal what
the
sheet name is? Example( I have a spreadsheet with about 30 sheets in it.
Each
sheet has a different name. Instead of haveing to name every sheet, and
tab
twice is there a way I can rename the tab and have a formula i the sheet
that
will name the sheet the same as the tab name?)

 

[Barb Reinhardt]

David,

I had this equation in A1 to begin with and had A1 in the formula and I got
an error for a circular reference.

Barb

Hi Barb,
Good to be on the lookout for circular references, but ...

Actually you won't get a circular reference, because it is not picking
up the value of A2. All that A2 in your reference is going to tell Excel
is the location you are interested in -- worksheet, workbook, pathname
etc.
You could use any cell on that sheet and get the same answer.
http://www.mvps.org/dmcritchie/excel/pathname.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Try this:
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-SEARCH("]",CELL("filename",A2)))

Just don't put it in A2 or you'll get a circular reference.

Hello,
Is there a way yo can put a formula in a cell to have it equal what
the
sheet name is? Example( I have a spreadsheet with about 30 sheets in
it.
Each
sheet has a different name. Instead of haveing to name every sheet, and
tab
twice is there a way I can rename the tab and have a formula i the
sheet
that
will name the sheet the same as the tab name?)

 

[David McRitchie]

Hi Barb,
I put the formula you used into A2, used the fill handle to fill in A1, then fill
down to A7 then as a group across to column 7, no problems with
in any of those cells A1:H7 -- I'm using Excel 2000 -- try it again.,

same for the one on my pathname.htm page
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)