&[filename] &[tab] added to cell

[Joel]

Is it possible to add the filename or worksheet tab name
to a cell?

 

[Frank Kabel]

Hi Joel
some formulas for that (workbook has to be saved once before they work)
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))

 

[David McRitchie]

Hi Joel,
See http://www.mvps.org/dmcritchie/excel/pathname.htm

specifically

1996FEDT.XLS
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",
CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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

The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).