Link a cell to file name

[schnett]

How do I link a cell to the name of the file

 

[Frank Kabel]

Hi
try one of the following formulas (note: the workbook has
to be save
before). Just use the formulas as they are shown (don't
replace
'filename' with anything else)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-
1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)
+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND
("]",CELL("file
name",A1),1))

 

[Don Guillett]

A good way to learn is:
1. in a cell put =
2. Open/activate the other file>goto the address desired>touch enter
3. go back to the original sheet
voila

 

[schnett]

Sorry, I didn't specify. I am interested in this formula.

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)
+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

I have an additional question. How do I use file name only without th
extension of ".xls"

 

[Don Guillett]

sorry I mis-understood.

 

[hgrove]

schnett wrote...
...

First, *ALWAYS* use the *quote* buttons in excelforum to reply t
messages. Even if you then delete all the quoted material, you wil
have retained the thread's Subject. Google Groups archive doesn't kee
threads together when subject lines change (modulo adding 'Re: ' at th
beginning). Post Reply buttons in excelforum doesn't automaticall
default the Subject to the thread's original subject, which is BAD, s
don't use that button.

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,
FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)
-1)

I have an additional question. How do I use file name only >withou

the extension of ".xls" ?

Change the formula to

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,
FIND(".xls]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),
1)-1

 

[schnett]

Thank you for the tips.

*schnett wrote...
...

First, *ALWAYS* use the *quote* buttons in excelforum to reply t
messages. Even if you then delete all the quoted material, you wil
have retained the thread's Subject. Google Groups archive doesn'
keep threads together when subject lines change (modulo adding 'Re:
at the beginning). Post Reply buttons in excelforum doesn'
automatically default the Subject to the thread's original subject
which is BAD, so don't use that button.

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,
FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)
-1)

I have an additional question. How do I use file name only >withou

the extension of ".xls" ?

Change the formula to

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,
FIND(".xls]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),
1)-1)