filename in spreadsheet 3

[jnims]

Is there an easy way to show a spreadsheet's filename and path without typing it in manually?

 

[mgp]

Type in the cell where you want the filame:

=CELL("filename&quot

that should do

regards
Mogens

 

[jnims]

Thanks Mogen, works like a charm.

 

[joerd]

I would advise you to use CELL("filename",A1) (or any other address of a cell on the sheet). That way you are sure that the filename+sheet tab name is always the name of the worksheet that the function is on (or more specifically, where the referenced cell is on).
Just using CELL("filename&quot can give funny results when working on multiple workbooks/sheets. Regards,

Joerd

 

[mgp]

Thats a good one joerd

I have experienced that, bud didn't know this option.

regards
Mogens

 

[SPEAR]

Any way to just display the filename and not the path ?

 

[joerd]

Yes, you have probably noticed that the CELL function gives you a long string, with the filename between square brackets.
So you "only" have to parse the string, find the square brackets, and return the part of the string between the brackets as the filename.
All in one, this gives you something like:

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1)

Have fun. Regards,

Joerd

 

[joerd]

By the way, same can be done more nicely in VBA, by a user defined function:

Function Filename(r As Range)
    Application.Volatile
    Filename = r.Worksheet.Parent.Name
End Function

Call this function from your sheet as =Filename(A1), for example. Regards,

Joerd

 

[SPEAR]

Thanks
I had tried other ways but I was being too simplistic