How to Build an External Link Using Data from Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to import data in a cell from an external
spreadsheet located at 'C:\Data\[MyXLS.xls]MySheet!$H$5.

I want to build it using cells from the current worksheet
such as:
A1 = C:\Data
A2 = MyXLS.xls
A3 = MySheet

In cell A4, I want to build the file location string and
have the value displayed. For example, in A4 if I enter
something similar to "='C:\Data\[MyXLS.xls]MySheet'!$H$5"
and if H5 contained 25, then 25 would display in A4.

This works fine, but when I try to build the formula
using values from cells A1, A2 and A3 it doesn't work.

Thanks in advance for any suggestions.
 
Hi

you have to use the function INDIRECT. So in your example enter the
following
=INDIRECT("'" & A1 & "\[" & A2 & "]" & A3 & "'!$H$5")
BUT this will work only if the second sheet is open. INDIRECT will not
work for closed files!.

If you have to reference closed files based on other cells, you may
have a look at the free add-in Morefunc.xll at
http://longre.free.fr/english/
This includes a function INDIRECT.EXT which will overcome Excels
restrictions regarding closed files.

Another note: INDIRECT.EXT won't work in combination with functions
like VLOOKUP and closed files. But if you only want to import data,
INDIRECT.EXT should work for you

HTH
Frank
 
you have to use the function INDIRECT. So in your example enter the
following
=INDIRECT("'" & A1 & "\[" & A2 & "]" & A3 & "'!$H$5")
BUT this will work only if the second sheet is open. INDIRECT will not
work for closed files!.
Click to expand...

'sheet'? The other workbook must be open.
If you have to reference closed files based on other cells, you may
have a look at the free add-in Morefunc.xll at
http://longre.free.fr/english/
This includes a function INDIRECT.EXT which will overcome Excels
restrictions regarding closed files.

Another note: INDIRECT.EXT won't work in combination with functions
like VLOOKUP and closed files. But if you only want to import data,
INDIRECT.EXT should work for you
Click to expand...

INDIRECT.EXT works just fine with VLOOKUP. None of the arguments to VLOOKUP need
to be ranges - any and all could be arrays (though array 4th arguments are
weird). There are few worksheet functions that require only ranges and never
arrays. Some of them are OFFSET, SUMIF, COUNTIF and RANK. There may be others,
but I'm too lazy to give an exhaustive list.
 
Back
Top