Sumproduct & Indirect on the fly

[Rob]

Hi,

I would like to enter a file name in to a workbook and have a formula use
this with sheet name and cell address to return the numeric contents of a
closed workbook. From what I've read and seen on several sites is that this
can be done only if both files are open. However, I've also read that
Sumproduct can read from closed files, therefore is there a way to achieve
what I need other than using Links.

Thanks, Rob

 

[Frank Kabel]

Hi
SUMPRODUCT WITHOUT Indirect can access closed files. But
not together with INDIRECT

 

[Arvi Laanemets]

Hi

With SUMPRODUCT (or almost any other function) you can use a link as range
reference. There are exactly same limitations as for simple link.

 

[Rob]

If I have to use a Link to gather info from a closed workbook, is it
possible to say enter a file name including full path in a cell and then
have VBA update the link?

If so any pointers of how to do as recording a macro hasn't worked.

Much obliged, Rob