×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

Best of Excel

How do I show the sheet name / filename in a cell by xlbo
Posted: 10 Oct 02 (Edited 5 Mar 04)

This is very easy with code. It's actually pretty easy with formulae as well:

To display the full title of the Workbook in a cell - including the Sheet name:

=CELL("FILENAME",F10)

eg. H:\MSOffice\Macros\[Accessing document properties.xls]Sheet1

To display the Path Including Workbook Name:

=MID(CELL("FILENAME",F10), 1,FIND("]",CELL("FILENAME",F10)))

eg. H:\MSOffice\Macros\[TestFile.xls]

To display the Path Excluding the Workbook Name:

=MID(CELL("FILENAME",F11), 1,FIND("[",CELL("FILENAME",F11))-1)

eg. H:\MSOffice\Macros\

To display the Filename:

=MID(CELL("FILENAME",F10),FIND("[",CELL("FILENAME",F10))+1,FIND("]",CELL("FILENAME",F10))-FIND("[",CELL("FILENAME",F10))-1)

eg. Accessing document properties.xls

To display the Sheet name:

=RIGHT(CELL("FILENAME",F10),LEN(CELL("FILENAME",F10))-FIND("]",CELL("FILENAME",F10)))

eg. Sheet1

Just enter the formula as above - very useful for referencing in code and linked formulae. The "F10" is just an arbitrary cell reference - it doesn't matter as long as it is consistent in the formula

Please note that these functions will not work until the workbook has been saved.

Credit for this must go to Julian Milano (Excel L list)

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close