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" <Marcus@discussions.microsoft.com> wrote in message
news:A6E5F27E-B7EA-4136-A305-A95971F9D854@microsoft.com...
> 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" <Marcus@discussions.microsoft.com> wrote in message
news:A6E5F27E-B7EA-4136-A305-A95971F9D854@microsoft.com...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:elsLSPV7FHA.476@TK2MSFTNGP15.phx.gbl...
> 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" <Marcus@discussions.microsoft.com> wrote in message
> news:A6E5F27E-B7EA-4136-A305-A95971F9D854@microsoft.com...
> > 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" <Marcus@discussions.microsoft.com> wrote in message
> news:A6E5F27E-B7EA-4136-A305-A95971F9D854@microsoft.com...
> > 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" <reply@tonewsgroup.com> wrote in message news:eHDFCOV7FHA.3976@TK2MSFTNGP15.phx.gbl...
> 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" <Marcus@discussions.microsoft.com> wrote in message
> news:A6E5F27E-B7EA-4136-A305-A95971F9D854@microsoft.com...
> > 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" <dmcritchie_xlmvp@verizon.net> wrote in message
news:uvPmTCW7FHA.4012@TK2MSFTNGP14.phx.gbl...
> 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" <reply@tonewsgroup.com> wrote in message
> news:eHDFCOV7FHA.3976@TK2MSFTNGP15.phx.gbl...
>> 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" <Marcus@discussions.microsoft.com> wrote in message
>> news:A6E5F27E-B7EA-4136-A305-A95971F9D854@microsoft.com...
>> > 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,
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)
---
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" <reply@tonewsgroup.com> wrote in message news:etf3vzX7FHA.3880@TK2MSFTNGP12.phx.gbl...
> 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.
>
> =RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-SEARCH("]",CELL("filename",A2)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks