This site will look better if you upgrade to a browser that supports web standards.

McGimpsey & Associates 

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

CELL() and names

Want to put the name of your sheet in a cell, and make it dynamic so that it changes when the sheet name changes? How about the name of your workbook? XL's CELL() function can do that for you.

CELL("filename", range_ref) returns the entire path and worksheet name of the active sheet. A couple of notes

• "filename" is the word "filename" (in quotes) - don't replace it with your workbook file's name
• the file must be saved in order for CELLS() to work
• the range_ref is optional. If it's left out, CELLS("filename") will return the sheet name of the cell that was activeated when the last calculation was done.

Here are some options for returning portions of the filename and path. Assume that these formulae are placed in Cell A1 of Sheet2 of Workbook Workbook1.xls, which is saved in the (Macintosh) Panther:Users:je:Documents:XL files folder.

Full file path, including worksheet name

    =CELL("filename",A1) 

returns (again, Mac path separators):

        Panther:Users:je:Documents:XL Files:[Workbook1.xls]Sheet2

Full file path

    =LEFT(CELL("filename",A1), FIND("]",CELL("filename", A1)))

returns

        Panther:Users:je:Documents:XL Files:[Workbook1.xls]

Path only

    =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1)) - 2)

returns

        Panther:Users:je:Documents:XL Files

File name only

    =MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1))- 1), FIND("[",CELL("filename", A1))+1, 255)

returns (note: there's nothing special about 255 - it's just a large number to make sure we get all of the filename):

        Workbook1.xls

Worksheet name only

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

returns

        Sheet2

This page last updated Wednesday, 21 July 2004

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.

Quick Links

Not familiar with macros? See David McRitchie's Getting Started With Macros

Where to put your code

About Event Macros>