------ Register to get rid of these "In Post" ads! ------


How do I display the filename and path in a cell? I dont want it in the
header - but I want it in a cell as I want to link to that cell from a word
document.
--
Diane

------ Register to get rid of these "In Post" ads! ------


Try the below in a saved workbook.

(all in one line)
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

If this post helps click Yes
---------------
Jacob Skaria


"DianePDavies" wrote:

> How do I display the filename and path in a cell? I dont want it in the
> header - but I want it in a cell as I want to link to that cell from a word
> document.
> --
> Diane

------ Register to get rid of these "In Post" ads! ------


Try:

=CELL("filename",$A$1)

this gives the entire path (including the sheetname).

__________________
Where there is a will there are many ways.

The Code Cage

------ Register to get rid of these "In Post" ads! ------


It doesn't work...

If I paste the suggestion into my cell - then that is just what is diplayed.

Same for the suggestion by NBVC.

So I am propably doing something wrong: I mark the cell and paste e.g.
=CELL("filename",$A$1)
in that cell. I can see that CELL is a function - but somehow it is not
recognized by my sheet? What do I have to set to activate this function?
--
Diane


"Jacob Skaria" wrote:

> Try the below in a saved workbook.
>
> (all in one line)
> =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "DianePDavies" wrote:
>
> > How do I display the filename and path in a cell? I dont want it in the
> > header - but I want it in a cell as I want to link to that cell from a word
> > document.
> > --
> > Diane

------ Register to get rid of these "In Post" ads! ------


Format the cell as General (or anything but Text).

Then reenter the formula.

The workbook has to be saved at least once for this to work, too.

If that doesn't help, maybe you're looking at formulas.

In xl2003 menus:
Tools|Options|View Tab|Uncheck Formulas

In any version:
ctrl-` (control-backquote, the key to the left of the 1/! on my USA keyboard).

DianePDavies wrote:
>
> It doesn't work...
>
> If I paste the suggestion into my cell - then that is just what is diplayed.
>
> Same for the suggestion by NBVC.
>
> So I am propably doing something wrong: I mark the cell and paste e.g.
> =CELL("filename",$A$1)
> in that cell. I can see that CELL is a function - but somehow it is not
> recognized by my sheet? What do I have to set to activate this function?
> --
> Diane
>
> "Jacob Skaria" wrote:
>
> > Try the below in a saved workbook.
> >
> > (all in one line)
> > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "DianePDavies" wrote:
> >
> > > How do I display the filename and path in a cell? I dont want it in the
> > > header - but I want it in a cell as I want to link to that cell from a word
> > > document.
> > > --
> > > Diane

--

Dave Peterson

------ Register to get rid of these "In Post" ads! ------


Is the cell formatted to text..

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)

If this post helps click Yes
---------------
Jacob Skaria


"DianePDavies" wrote:

> It doesn't work...
>
> If I paste the suggestion into my cell - then that is just what is diplayed.
>
> Same for the suggestion by NBVC.
>
> So I am propably doing something wrong: I mark the cell and paste e.g.
> =CELL("filename",$A$1)
> in that cell. I can see that CELL is a function - but somehow it is not
> recognized by my sheet? What do I have to set to activate this function?
> --
> Diane
>
>
> "Jacob Skaria" wrote:
>
> > Try the below in a saved workbook.
> >
> > (all in one line)
> > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "DianePDavies" wrote:
> >
> > > How do I display the filename and path in a cell? I dont want it in the
> > > header - but I want it in a cell as I want to link to that cell from a word
> > > document.
> > > --
> > > Diane

------ Register to get rid of these "In Post" ads! ------


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

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)

These formulas could return an incorrect result if you have more than one
file open at the same time. You need to include a cell reference (any cell
reference):

CELL("filename"),A1)

--
Biff
Microsoft Excel MVP


"Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message
news:B243C918-E14B-4BE4-9D22-A56B9364C7E3@microsoft.com...
> Is the cell formatted to text..
>
> =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "DianePDavies" wrote:
>
>> It doesn't work...
>>
>> If I paste the suggestion into my cell - then that is just what is
>> diplayed.
>>
>> Same for the suggestion by NBVC.
>>
>> So I am propably doing something wrong: I mark the cell and paste e.g.
>> =CELL("filename",$A$1)
>> in that cell. I can see that CELL is a function - but somehow it is not
>> recognized by my sheet? What do I have to set to activate this function?
>> --
>> Diane
>>
>>
>> "Jacob Skaria" wrote:
>>
>> > Try the below in a saved workbook.
>> >
>> > (all in one line)
>> > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
>> >
>> > If this post helps click Yes
>> > ---------------
>> > Jacob Skaria
>> >
>> >
>> > "DianePDavies" wrote:
>> >
>> > > How do I display the filename and path in a cell? I dont want it in
>> > > the
>> > > header - but I want it in a cell as I want to link to that cell from
>> > > a word
>> > > document.
>> > > --
>> > > Diane

------ Register to get rid of these "In Post" ads! ------


>You need to include a cell reference (any cell reference):
>CELL("filename"),A1)

Using the correct syntax would help!

CELL("filename",A1)


--
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@comcast.net> wrote in message
news:eROjdca%23JHA.5092@TK2MSFTNGP03.phx.gbl...
> =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
>
> =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)
>
> These formulas could return an incorrect result if you have more than one
> file open at the same time. You need to include a cell reference (any cell
> reference):
>
> CELL("filename"),A1)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message
> news:B243C918-E14B-4BE4-9D22-A56B9364C7E3@microsoft.com...
>> Is the cell formatted to text..
>>
>> =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)
>>
>> If this post helps click Yes
>> ---------------
>> Jacob Skaria
>>
>>
>> "DianePDavies" wrote:
>>
>>> It doesn't work...
>>>
>>> If I paste the suggestion into my cell - then that is just what is
>>> diplayed.
>>>
>>> Same for the suggestion by NBVC.
>>>
>>> So I am propably doing something wrong: I mark the cell and paste e.g.
>>> =CELL("filename",$A$1)
>>> in that cell. I can see that CELL is a function - but somehow it is not
>>> recognized by my sheet? What do I have to set to activate this function?
>>> --
>>> Diane
>>>
>>>
>>> "Jacob Skaria" wrote:
>>>
>>> > Try the below in a saved workbook.
>>> >
>>> > (all in one line)
>>> > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
>>> >
>>> > If this post helps click Yes
>>> > ---------------
>>> > Jacob Skaria
>>> >
>>> >
>>> > "DianePDavies" wrote:
>>> >
>>> > > How do I display the filename and path in a cell? I dont want it in
>>> > > the
>>> > > header - but I want it in a cell as I want to link to that cell from
>>> > > a word
>>> > > document.
>>> > > --
>>> > > Diane
>
>

------ Register to get rid of these "In Post" ads! ------


Problem solved!

It appears that if I select a cell and paste the code in - then it doesn't
work. But if I manually type the same code:

=CELL("filename")

then it works and shows the path, the file and the current sheet. I have to
work on the formatting of the actual file name - but that can be solved.

--
Diane


"T. Valko" wrote:

> >You need to include a cell reference (any cell reference):
> >CELL("filename"),A1)
>
> Using the correct syntax would help!
>
> CELL("filename",A1)
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:eROjdca%23JHA.5092@TK2MSFTNGP03.phx.gbl...
> > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
> >
> > =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)
> >
> > These formulas could return an incorrect result if you have more than one
> > file open at the same time. You need to include a cell reference (any cell
> > reference):
> >
> > CELL("filename"),A1)
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message
> > news:B243C918-E14B-4BE4-9D22-A56B9364C7E3@microsoft.com...
> >> Is the cell formatted to text..
> >>
> >> =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)
> >>
> >> If this post helps click Yes
> >> ---------------
> >> Jacob Skaria
> >>
> >>
> >> "DianePDavies" wrote:
> >>
> >>> It doesn't work...
> >>>
> >>> If I paste the suggestion into my cell - then that is just what is
> >>> diplayed.
> >>>
> >>> Same for the suggestion by NBVC.
> >>>
> >>> So I am propably doing something wrong: I mark the cell and paste e.g.
> >>> =CELL("filename",$A$1)
> >>> in that cell. I can see that CELL is a function - but somehow it is not
> >>> recognized by my sheet? What do I have to set to activate this function?
> >>> --
> >>> Diane
> >>>
> >>>
> >>> "Jacob Skaria" wrote:
> >>>
> >>> > Try the below in a saved workbook.
> >>> >
> >>> > (all in one line)
> >>> > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
> >>> >
> >>> > If this post helps click Yes
> >>> > ---------------
> >>> > Jacob Skaria
> >>> >
> >>> >
> >>> > "DianePDavies" wrote:
> >>> >
> >>> > > How do I display the filename and path in a cell? I dont want it in
> >>> > > the
> >>> > > header - but I want it in a cell as I want to link to that cell from
> >>> > > a word
> >>> > > document.
> >>> > > --
> >>> > > Diane
> >
> >
>
>
>

------ Register to get rid of these "In Post" ads! ------


Read Biff's message again.

You'll want to use:

=cell("Filename",a1)

Otherwise, you may find that the value isn't what you expect--you'll see the
file and sheet name that was active when excel last calculated.

DianePDavies wrote:
>
> Problem solved!
>
> It appears that if I select a cell and paste the code in - then it doesn't
> work. But if I manually type the same code:
>
> =CELL("filename")
>
> then it works and shows the path, the file and the current sheet. I have to
> work on the formatting of the actual file name - but that can be solved.
>
> --
> Diane
>
> "T. Valko" wrote:
>
> > >You need to include a cell reference (any cell reference):
> > >CELL("filename"),A1)
> >
> > Using the correct syntax would help!
> >
> > CELL("filename",A1)
> >
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "T. Valko" <biffinpitt@comcast.net> wrote in message
> > news:eROjdca%23JHA.5092@TK2MSFTNGP03.phx.gbl...
> > > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
> > >
> > > =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)
> > >
> > > These formulas could return an incorrect result if you have more than one
> > > file open at the same time. You need to include a cell reference (any cell
> > > reference):
> > >
> > > CELL("filename"),A1)
> > >
> > > --
> > > Biff
> > > Microsoft Excel MVP
> > >
> > >
> > > "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message
> > > news:B243C918-E14B-4BE4-9D22-A56B9364C7E3@microsoft.com...
> > >> Is the cell formatted to text..
> > >>
> > >> =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",)
> > >>
> > >> If this post helps click Yes
> > >> ---------------
> > >> Jacob Skaria
> > >>
> > >>
> > >> "DianePDavies" wrote:
> > >>
> > >>> It doesn't work...
> > >>>
> > >>> If I paste the suggestion into my cell - then that is just what is
> > >>> diplayed.
> > >>>
> > >>> Same for the suggestion by NBVC.
> > >>>
> > >>> So I am propably doing something wrong: I mark the cell and paste e.g.
> > >>> =CELL("filename",$A$1)
> > >>> in that cell. I can see that CELL is a function - but somehow it is not
> > >>> recognized by my sheet? What do I have to set to activate this function?
> > >>> --
> > >>> Diane
> > >>>
> > >>>
> > >>> "Jacob Skaria" wrote:
> > >>>
> > >>> > Try the below in a saved workbook.
> > >>> >
> > >>> > (all in one line)
> > >>> > =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
> > >>> >
> > >>> > If this post helps click Yes
> > >>> > ---------------
> > >>> > Jacob Skaria
> > >>> >
> > >>> >
> > >>> > "DianePDavies" wrote:
> > >>> >
> > >>> > > How do I display the filename and path in a cell? I dont want it in
> > >>> > > the
> > >>> > > header - but I want it in a cell as I want to link to that cell from
> > >>> > > a word
> > >>> > > document.
> > >>> > > --
> > >>> > > Diane
> > >
> > >
> >
> >
> >

--

Dave Peterson