reference a cell by the path of another cell

Question

Hi, 

I would like to know if it is possible to reference a cell value like the below:

Sheet1!A1 = C:\abc[abc.xlsx]fileTab

Sheet2!B1 = "'"&Sheet1!A1&"'!B1"

Is there any formula I can make use to retrieve the data from another closed workbook, which its path specified in cell Sheet1!A1 ?

Currently, the output is the path in text, but I need the value.

Many Thanks,

Eddy

Answer 1

As you are likely aware, the INDIRECT() function can be used to construct/convert a text based representation of a cell or sheet!cell reference to a usable cell/range reference. However, this native function does not work with external references.

There is a widely known set of Excel functions in the free MoreFunc Excel Add-in XLA that contains a derived INDIRECT.EXT() function that will grant the same construction/conversion to an external address and there is a version compatible with Excel 2010.

Be very careful of downloading this set of library functions as there are fake/hacked versions cropping up around the Internet. I believe the following is safe for version 5.05.

MoreFunc version 5.05 from FreewareFiles.com

For the record, I personally feel that the INDIRECT.EXT() should only be used in absolute necessity where no other native method would do. It should not be used as blanket 'fix-all' to poor worksheet/workbook design.

Answer 2

Hi,

Try this

http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

Hope this helps.

Answer 3

Hi,

 

Try this

 

http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

 

Hope this helps.

Hi, Ashish Mathur

thanks so much for your help.

Since I am required not to hard code the path in the INDIRECT function, I would like to do something like  the below:

=INDIRECT("'"&Sheet1!A1)

But it seems it is not working when I write it like this, just wondering if I can have a workaround for this instead of writing macro.

Thanks again.

Answer 4

As you are likely aware, the INDIRECT() function can be used to construct/convert a text based representation of a cell or sheet!cell reference to a usable cell/range reference. However, this native function does not work with external references.

There is a widely known set of Excel functions in the free MoreFunc Excel Add-in XLA that contains a derived INDIRECT.EXT() function that will grant the same construction/conversion to an external address and there is a version compatible with Excel 2010.

Be very careful of downloading this set of library functions as there are fake/hacked versions cropping up around the Internet. I believe the following is safe for version 5.05.

MoreFunc version 5.05 from FreewareFiles.com

For the record, I personally feel that the INDIRECT.EXT() should only be used in absolute necessity where no other native method would do. It should not be used as blanket 'fix-all' to poor worksheet/workbook design.

Thanks so much Jeeped. But I don't have the access to install any things in the PC.

Answer 5

Hi,

The INDIRECT() function will not extract data from a closed workbook.  To do so, you will have to use the INDIRECT.EXT function which is available in the Morefunc addin (can be downloaded from the link which I shared with you in my earlier post).

Once you have downloaded and installed the addin, you can use the INDIRECT.EXT function to extract data from a closed file.