Converting Text to an external cell ref.

  • Thread starter Thread starter Wyvern
  • Start date Start date
W

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.
 
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
 
Back
Top