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?)

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.

"Marcus" wrote in message
...
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?)

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)


"Marcus" wrote in message
...
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?)

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)


"Bob Phillips" wrote in message
...
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)


"Marcus" wrote in message
...
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?)

This worked Barb,
Thank you.

"Barb Reinhardt" wrote:

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.

"Marcus" wrote in message
...
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?)

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

"Barb Reinhardt" wrote in message ...
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.

"Marcus" wrote in message
...
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,

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
"David McRitchie" wrote in message
...
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

"Barb Reinhardt" wrote in message
...
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.

"Marcus" wrote in message
...
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?)