excel 97

  • Thread starter Thread starter Terri S
  • Start date Start date
T

Terri S

Hello,
Is there a way to make the value of a cell equal the name of the tab?

Thanks
Terri
 
Hi
try one of the following formulas (note: the workbook has
to be save
before). Just use the formulas as they are shown (don't
replace
'filename' with anything else)

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))
 
Terri

From a post by Chip Pearson............

Use
=CELL("filename",A1)
to get the full name, including the sheet name. E.g.,
C:\Temp\[Test.Xls]Sheet1

Use
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename"
,A1))-FIND("]",CELL("filename",A1)))
to get just the sheet name
Sheet1


Use
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("file
name",A1))-FIND("[",CELL("filename",A1))-1)
to get just the file name
Test.Xls

Use
=LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)))
to get full name without sheet name
C:\Temp\[Test.xls]

Use
=SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)
)),"[",""),"]","")
to get full name without sheet name, and without square brackets
C:\Temp\Test.Xls


Gord Dibben Excel MVP
 
Hi

You could put something like this in the Workbook_Open sub.

Application.ScreenUpdating = False

For i = 1 To Sheets.Count
Sheets(i).Activate
ActiveSheet.Range("a1") = ActiveSheet.Name
Next

Sheets(1).Activate

Application.ScreenUpdating = True


HTH

Ken
 
Back
Top