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

Try this

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

Mike

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

 

[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

 

[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

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

Rick

Try this

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

Mike

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

 

[Rick Rothstein \(MVP - VB\)]

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

Thanks for correcting the correction. <g>

Rick

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

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

Rick

Try this

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

Mike

:

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

 

[balder]

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

Rune

Try this

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

Mike

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

 

[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

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

Rune

Try this

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

Mike

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

 

[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

Try this

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

Mike

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]

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

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

Try this

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

Mike

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

 

[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--

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Gord Dibben]

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

No tab name from cell code.


Gord