Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 5th 08, 04:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 2
Default how can I make a cell content equal the tab name 'sheet1' ?

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

  #2   Report Post  
Old April 5th 08, 05:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default how can I make a cell content equal the tab name 'sheet1' ?

Try this

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

Mike

"balder" wrote:

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

  #3   Report Post  
Old April 5th 08, 06:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1
Default how can I make a cell content equal the tab name 'sheet1' ?

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

Rick


"Mike H" wrote in message
...
Try this

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

Mike

"balder" wrote:

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


  #4   Report Post  
Old April 5th 08, 06:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1
Default how can I make a cell content equal the tab name 'sheet1' ?

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


  #5   Report Post  
Old April 5th 08, 07:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default how can I make a cell content equal the tab name 'sheet1' ?

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)" wrote:

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

Rick


"Mike H" wrote in message
...
Try this

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

Mike

"balder" wrote:

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





  #6   Report Post  
Old April 5th 08, 08:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1
Default how can I make a cell content equal the tab name 'sheet1' ?

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

Thanks for correcting the correction. <g

Rick


"Mike H" wrote in message
...
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)" wrote:

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

Rick


"Mike H" wrote in message
...
Try this

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

Mike

"balder" wrote:

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




  #7   Report Post  
Old April 5th 08, 09:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 2
Default how can I make a cell content equal the tab name 'sheet1' ?



"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

Try this

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

Mike

"balder" wrote:

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

  #8   Report Post  
Old April 5th 08, 10:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
Default how can I make a cell content equal the tab name 'sheet1' ?

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
wrote:



"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

Try this

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

Mike

"balder" wrote:

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


  #9   Report Post  
Old April 5th 08, 11:17 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 20
Default how can I make a cell content equal the tab name 'sheet1' ?

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" wrote:

Try this

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

Mike

"balder" wrote:

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

  #10   Report Post  
Old April 6th 08, 09:12 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default how can I make a cell content equal the tab name 'sheet1' ?

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

"JohnL" wrote:

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" wrote:

Try this

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

Mike

"balder" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
make a cell equal a worksheet name cyndi Excel Discussion (Misc queries) 3 June 22nd 06 09:31 PM
can I make worksheet name equal a cell flyingdoc Excel Worksheet Functions 3 July 22nd 05 03:19 PM
can i type sheet1 A5 and make it show Sheet2 A6 cell HelpMe Excel Discussion (Misc queries) 1 February 25th 05 01:44 AM
can I make cell "yes" equal 1, "no" equal 0 can I make cell yes equal 1, no equa Excel Discussion (Misc queries) 3 January 25th 05 08:57 PM
How do I make one cell equal to another Deb Blackshaw Excel Worksheet Functions 2 December 20th 04 12:29 AM


All times are GMT +1. The time now is 07:52 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017