Converting Text to an external cell ref.

[Wyvern]

Hi, I'm, trying to put together an external cell reference in my
worksheet. I can join together text that looks like the File Path /
Work Sheet / Cell reference, but I can't figure out how to turn this
string of text into a file path etc. and pick up the data I want.

I get this strange feeling I'm being a complete idiot asking this can't
see the wood for the trees, but I need help.

 

[Frank Kabel]

Hi
normally you can use INDIRECT for this. E.g.
=INDIRECT("'[Book1.xls]Sheet1'!A1")
which gets the value from cell A1, sheet 1 in book1.xls. So using
INDIRECT you can turn your string into a 'real' cell reference. But
this works only if the other book is opened!. If book1.xls is closed
INDIRECT won't work. There are some solutions to overcome this
restriction.
You may try the free add-in MOREFUNC.xll
(http://longre.free.fr/english) which implements the function
INDIRECT.EXT. E.g.
INDIRECT.EXT("'C:\TEMP\[book1.xls].Sheet1'!A1") will work also for
closed workbooks

HTH
Frank

 

[Wyvern]

Hey Frank, many thanks for your reply, I'm sorted now. Thanks agai
Geoff

 

[Frank Kabel]

Hi Geoff
thanks for the thanks
Frank