Hello Excellers and welcome to another #formulafriday today let’s look at a request I have had from a reader, and that is to insert the file path and name of the file in a cell on the front of a workbook (or the first sheet). This actually is a really useful piece of information, to know exactly where your file is saved either locally or in a network location.
I use this a lot as would have a need to know if my files are locally stored or on a shared drive using multiple report folders on a daily basis.
So, how do we easily get the file name and path. We can use the little (in my experience) used CELL formula. The syntax of the CELL formula is
info_type – This argument is required and is a text value that specifies what type of cell information you want to return. The following list shows the possible values of the Info_type argument and the corresponding results.
If you select the “format” value to be returned then the following then the values will be returned as follows:-
[reference] This optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.
The following list describes the text values that the CELL function returns when the Info_type argument is “format” and the reference argument is a cell that is formatted with a built-in number format
So in this example we are using the info_type “filename” and type it into cell A1 of my workbook. It returns the full file path right down to the current worksheet. I have this workbook saved on my desktop and the current worksheet is call CELL FORMULA.
If you use CELL formula in a workbook that has not yet been saved then the formula returns a blank result ” “. Below is an new unsaved Excel workbook.
Now It’s Your Turn!
Have you ever used this function at all, do you think it would be useful?.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.