This should work:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
John
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND
("]",CELL("filename",A1)))
Workbook must be saved for this to work.
HTH
Jason
Atlanta, GA
>.
>
The crippled form =cell("filename") indicates the last sheet update,
which can be useful if you want to see the sheetname that was last
updated, though this is not reliable because a recalculation will
change the value to the activesheet or the first sheet in a grouping
at the time of recalculation. A recalculation occurs when the workbook
is opened. See HELP, Cell worksheet function; for more information.
--
You can test this out by opening two workbooks and placing this
into a cell in one of them
=CELL("filename")
then change a cell in the other workbook, and return to the first.
I don't think that is the answer you want to see.
I believe I have pointed this out to you (John) several times in the past.
Jason's answer would be correct, as it does include the cell reference.
The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error). .
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"John Wilson" <jwi...@optonline.net> wrote in message news:3E109F28...@optonline.net...
I don't really remember that you ever pointed this out to me in the past.
Regardless, you are correct and I should be more careful when I shoot
from the hip to answer a question.
Funny thing is that I found the correct code in google, couldn't figure
out why the "A1" cell reference was there, deleted it, tested it and
it "seemed" to work.
Thanks for keeping me honest.
Visited your link on this subject and learned a lot from it.
Next time I see this question again, I'll be sure to provide the link
to your site in lieu of exposing my ignorance.
John
Excel is braindead in some respects. In this case, Windows and presumably Mac OS
allow square brackets in filenames, so while the formula above may work most of
the time, it could break down in unusual (but NOT pathological) situations.
Better to search for the rightmost backslash (in Windows - is the Mac OS folder
separator still colon or has it become slash?).
In order to avoid a single monstrous formula, it's possible to use defined names
for this. For instance,
__UGH referring to =CELL("Filename")
__ASN referring to
=RIGHT(__UGH,MATCH("]",MID(__UGH,LEN(__UGH)+1
-ROW(INDIRECT("1:"&LEN(__UGH))),1),0)-1)
WBDN referring to
=LEFT(__UGH,LEN(__UGH)-MATCH("\",MID(__UGH,LEN(__UGH)+1
-ROW(INDIRECT("1:"&LEN(__UGH))),1),0))
WBBN referring to
=MID(__UGH,LEN(WBDN)+3,LEN(__UGH)-LEN(WBDN)-LEN(__ASN)-3)
and for the heck of it
WSN referring to
=MID(CELL("Filename",INDIRECT("A1")),LEN(WBDN)+LEN(WBBN)+4,255)
With this infrastructure, WBDN gives the workbook's directory name, WBBN gives
the workbook's base filename, and WSN gives the worksheet name for the worksheet
containing the formula evaluating itself (so =WSN in Sheet1!A1 gives Sheet1
while =WSN in 'Foo Bar'!X99 gives Foo Bar).
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
__UGH referring to =CELL("Filename",INDIRECT("A1"))
Why the INDIRECT? *Cut* and paste onto cell A1, delete row 1 or column A, and a
bare A1 or $A$1 would become #REF!. INDIRECT prevents that.
"John Wilson" <jwi...@optonline.net> wrote in message news:3E10ABD5...@optonline.net...
> Thanks for keeping me honest.
> Visited your link on this subject and learned a lot from it.
> Next time I see this question again, I'll be sure to provide the link
> to your site in lieu of exposing my ignorance.
>
Re: Not using second argument in:
CELL("filename",A1)
You probably ignored requirement at first because the reason why was not
explained. It's not obvious until you hit a problem with leaving out the
second argument. That's one function of referring people to site's such as
yours for "further and better particulars." But I think a tad of
explanation, if short, takes the poster further.
Regards
--
Norman Harker
Sydney, Australia.
Massive free firework display tonight!
If you missed Christmas there's another on 7 January!
Happy New Year
(Whenever you celebrate it and whatever number you give it)
njha...@optusnet.com.au
http://makeashorterlink.com/?M42B36AE2
Jason
>-----Original Message-----
>Hi John,
>It usually takes a couple of times to really learn
something
>correctly -- it believe it was David Hager or possibly
Alan Beban
>that got me straight on this one to include the reference
cell --
>couldn't find where I got corrected
>so may have been in email and it finally sank in during
Aug 1999.
>I think I ignored originally being told because I
couldn't see a
>difference for lack of a proper decriptive example or
laziness on
>my part.
>.. ---
>HTH,
>David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
>My Excel Macros:
http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
>
>"John Wilson" <jwi...@optonline.net> wrote in message
news:3E10ABD5...@optonline.net...
>> Thanks for keeping me honest.
>> Visited your link on this subject and learned a lot
from it.
>> Next time I see this question again, I'll be sure to
provide the link
>> to your site in lieu of exposing my ignorance.
>>
>> David McRitchie wrote:
>> > You should not be using crippled form of CELL
("filename") instead
>> > of CELL("filename",A1) for more information see
>> > Pathname in headings, footers, and cells
>> > http://www.mvps.org/dmcritchie/excel/pathname.htm
>
>
>.
>
> Excel is braindead in some respects. In this case, Windows and
> presumably Mac OS allow square brackets in filenames
Yes, MacOS allows square brackets as well as forward and backslashes
> , so while the
> formula above may work most of the time, it could break down in
> unusual (but NOT pathological) situations. Better to search for the
> rightmost backslash (in Windows - is the Mac OS folder separator
> still colon or has it become slash?).
MacOS uses colons, but the Darwin/FreeBSD side uses slashes.
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
see http://www.mvps.org/dmcritchie/excel/pathname.htm for
additional items you can extract from CELL("filename",A1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Jim Stoicheff" <jims...@hotmail.com> wrote in message news:043501c2b064$a21ad1c0$d3f82ecf@TK2MSFTNGXA10...