Worksheet Name

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
 
Hi Bill
try the following - a little more than asked for
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))
 
Frank,

How would i adopt your formulas to print the file name
with worksheet name as a footer? Is it possible?

Thanks

Samir
-----Original Message-----
Hi Bill
try the following - a little more than asked for
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))

--
Regards
Frank Kabel
Frankfurt, Germany
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
Click to expand...
 
Hi
what Excel version are you using?

--
Regards
Frank Kabel
Frankfurt, Germany
Frank,

How would i adopt your formulas to print the file name
with worksheet name as a footer? Is it possible?

Thanks

Samir
-----Original Message-----
Hi Bill
try the following - a little more than asked for
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))

--
Regards
Frank Kabel
Frankfurt, Germany
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
Click to expand...
Click to expand...
 
Hi

When you want to return the name of some other sheet (not the one the
formula is placed on), then

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


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


JMay said:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


Bill said:
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
Click to expand...
 
Excel 2000. Sorry I should have mentioned and I should
have checked for replies early. Today is very hectic!
Thanks.

Regards

Samir
-----Original Message-----
Hi
what Excel version are you using?

--
Regards
Frank Kabel
Frankfurt, Germany
Frank,

How would i adopt your formulas to print the file name
with worksheet name as a footer? Is it possible?

Thanks

Samir
-----Original Message-----
Hi Bill
try the following - a little more than asked for
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))

--
Regards
Frank Kabel
Frankfurt, Germany

Bill wrote:
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
Click to expand...
.
Click to expand...
 
Samir

For Excel 2000 you must use a macro to get path and name in footer.

Sub PathInFooter()
ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & _
ActiveSheet.Name & " " & Application.UserName & " " & Date
End Sub

OR to use what is in A1 if you have used Frank's formula(s)

Sub CellInFooter()
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").text
End With
End Sub

Either of these can be placed in the Before_Print code if desired.

Gord Dibben Excel MVP

Excel 2000. Sorry I should have mentioned and I should
have checked for replies early. Today is very hectic!
Thanks.

Regards

Samir
-----Original Message-----
Hi
what Excel version are you using?

--
Regards
Frank Kabel
Frankfurt, Germany
Frank,

How would i adopt your formulas to print the file name
with worksheet name as a footer? Is it possible?

Thanks

Samir

-----Original Message-----
Hi Bill
try the following - a little more than asked for
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))

--
Regards
Frank Kabel
Frankfurt, Germany

Bill wrote:
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
.
Click to expand...
Click to expand...
 
Hi
you may put the following code in your workbook module (will add the
name to all of your footers)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wbk_name
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
wbk_name = ActiveWorkbook.FullName & " " & _
wkSht.Name
With wkSht.PageSetup
.CenterFooter = wbk_name
End With
Next wkSht
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany
Excel 2000. Sorry I should have mentioned and I should
have checked for replies early. Today is very hectic!
Thanks.

Regards

Samir
-----Original Message-----
Hi
what Excel version are you using?

--
Regards
Frank Kabel
Frankfurt, Germany
Frank,

How would i adopt your formulas to print the file name
with worksheet name as a footer? Is it possible?

Thanks

Samir

-----Original Message-----
Hi Bill
try the following - a little more than asked for
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))

--
Regards
Frank Kabel
Frankfurt, Germany

Bill wrote:
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
.
Click to expand...
Click to expand...
 
Thanks a lot. This is useful and will save lot of looking.
Thanks again.

Regards
Samir
-----Original Message-----
Hi
you may put the following code in your workbook module (will add the
name to all of your footers)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wbk_name
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
wbk_name = ActiveWorkbook.FullName & " " & _
wkSht.Name
With wkSht.PageSetup
.CenterFooter = wbk_name
End With
Next wkSht
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany
Excel 2000. Sorry I should have mentioned and I should
have checked for replies early. Today is very hectic!
Thanks.

Regards

Samir
-----Original Message-----
Hi
what Excel version are you using?

--
Regards
Frank Kabel
Frankfurt, Germany

Samir wrote:
Frank,

How would i adopt your formulas to print the file name
with worksheet name as a footer? Is it possible?

Thanks

Samir

-----Original Message-----
Hi Bill
try the following - a little more than asked for
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))

--
Regards
Frank Kabel
Frankfurt, Germany

Bill wrote:
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
.
Click to expand...
Click to expand...

.
Click to expand...
 
Thanks Arvi;
Added it my library,, did note that if the calling sheetname has spaces in
the name,
it returns an #Name!, or the like.. probably some added syntax to work
around.. aye?
JMay


Arvi Laanemets said:
Hi

When you want to return the name of some other sheet (not the one the
formula is placed on), then

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


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


JMay said:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


Bill said:
Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
Click to expand...
Click to expand...
 
JMay said:
Thanks Arvi;
Added it my library,, did note that if the calling sheetname has spaces in
the name,
it returns an #Name!, or the like.. probably some added syntax to work
around.. aye?
Click to expand...

=MID(CELL("filename",'Sheet name'!A1),FIND("]",CELL("filename",'Sheet
name'!A1))+1,255)

Arvi Laanemets

JMay


Arvi Laanemets said:
Hi

When you want to return the name of some other sheet (not the one the
formula is placed on), then
Click to expand...
=MID(CELL("filename",Sheetname!A1),FIND("]",CELL("filename",Sheetname!A1))+1
,255)


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


JMay said:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


Is there some function that will allow you to pick up the
name (on the tab) of a worksheet and populate a cell with
the name? So that if you change the name of the
worksheet, the cell value will change to match?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top