Copy worksheet name to/from cell

[Brian]

I have an expenses workbook, which has the same format on each sheet. The
month appears in the same cell of the sheet each time, and I have the month
repeated (in the same format) as the sheet name on the tab.

Is there any way that the cell can automatically pick up the month from the
sheet tab (or vice versa?)

Perhaps this can only be done in VB?

 

[JMay]

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

 

[Gord Dibben]

Brian

From a post by Frank Kabel to place the sheet name in a cell..........

Hi
try the following formulas (work after you saved the workbook the first
time. Don't replace "filename" with anything, leave the formulas as
they are)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

To place a cell value as the sheet name use.........

Sub SheetName()
ActiveSheet.Name = Range("c1")
End Sub

Gord Dibben Excel MVP

 

[Brian]

Many thanks for your help. Always learning something new...

Brian

From a post by Frank Kabel to place the sheet name in a cell..........

Hi
try the following formulas (work after you saved the workbook the first
time. Don't replace "filename" with anything, leave the formulas as
they are)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

To place a cell value as the sheet name use.........

Sub SheetName()
ActiveSheet.Name = Range("c1")
End Sub

Gord Dibben Excel MVP

 

[Gord Dibben]

Thanks for the feedback Brian.

Gord

Many thanks for your help. Always learning something new...

Brian

From a post by Frank Kabel to place the sheet name in a cell..........

Hi
try the following formulas (work after you saved the workbook the first
time. Don't replace "filename" with anything, leave the formulas as
they are)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

To place a cell value as the sheet name use.........

Sub SheetName()
ActiveSheet.Name = Range("c1")
End Sub

Gord Dibben Excel MVP

 

[Tom Ogilvy]

You can find similar to these at the bottom of this page at Chip Pearson's
site:

http://www.cpearson.com/excel/excelF.htm

It also has many other general use formulas you might find useful.

--
Regards,
Tom Ogilvy

Many thanks for your help. Always learning something new...

Brian

From a post by Frank Kabel to place the sheet name in a cell..........

Hi
try the following formulas (work after you saved the workbook the first
time. Don't replace "filename" with anything, leave the formulas as
they are)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

To place a cell value as the sheet name use.........

Sub SheetName()
ActiveSheet.Name = Range("c1")
End Sub

Gord Dibben Excel MVP