how can I make a cell content equal the tab name 'sheet1' ?

[balder]

Is there a way to make a cell content in a spreadsheet equal to the tab name
of that spread sheet w/o having to type the tab name into the cell?
Like b1="tab name" where tab name would be copied into cell b1

[Mike H]

Try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Mike

[Rick Rothstein (MVP - VB)]

Just a follow-up note on Mike's formula... it will only work if the
worksheet has been saved.

Rick

"Mike H" <Mi...@discussions.microsoft.com> wrote in message
news:7F67AF84-B5C5-4228...@microsoft.com...

[Rick Rothstein (MVP - VB)]

Another possibility is to run this macro from the cell you want to place the
worksheet tab's name in...

Sub PutTabNameInActiveCell()
ActiveCell.Value = ActiveSheet.Name
End Sub

Rick

"balder" <bal...@discussions.microsoft.com> wrote in message
news:1C421EA7-F849-4FDE...@microsoft.com...

[Mike H]

I think you may have meant if the workbook has been saved, it works fine if
you add a new sheet to a saved workbook and use the formula in that new
unsaved sheet

[Rick Rothstein (MVP - VB)]

Yes, **workbook**, not my mistyped **worksheet**.

Thanks for correcting the correction. <g>

Rick

"Mike H" <Mi...@discussions.microsoft.com> wrote in message

news:3847BDF2-9C44-400B...@microsoft.com...

[balder]

"Mike H" wrote:
It worked!! Thank you very much. Another challenge perhaps - how to make a
tab name equal the content of a spreadsheet cell?

Rune

[Gord Dibben]

Event code from Bob Phillips to change tab to cell contents.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Adjust "A1" to suit then Alt + q to go back
to the Excel window to test by entering a value in A1

Gord Dibben MS Excel MVP

On Sat, 5 Apr 2008 13:47:00 -0700, balder <bal...@discussions.microsoft.com>
wrote:

[JohnL]

Hello Mike H. Sorry to intrude. I like to learn from these questions.

I too tried it and it works. But what is the significance
of " +1,256" ?

TIA

JohnL

[Mike H]

Hi,

Try the formula
=CELL("filename",A1)
and you see the full path

The +1 is part of the Find function and it gives the start number where it
finds the ] character in that string and for the sheet name you start 1
character after that or +1

The 256 is how many characters to display for the Sheet name. reduce this to
2 and you get the first 2 characters . 256 is overkill but it does ensure you
get the full sheet name.

Mike

[ryguy7272]

I like that code Gord! This is my favorite site for dynamic tab names and
such:
http://www.mcgimpsey.com/excel/formulae/cell_function.html

Regards,
Ryan--
--
RyGuy

"Rick Rothstein (MVP - VB)" wrote:

[Rick Rothstein (MVP - VB)]

Did you mean to post your message against my posting (you said Gord in your
message)?

Rick

"ryguy7272" <rygu...@discussions.microsoft.com> wrote in message
news:06190204-969B-45F6...@microsoft.com...

[Gord Dibben]

All I see at that site is tab name to cell formulas.

No tab name from cell code.

Gord