External cell references using INDIRECT & ADDRESS

[Conan Kelly]

Hello all,

If I put an external cell reference (a reference to a cell in another file)
in a cell, I do not need that workbook open in order for that value to be
updated.

But (and correct me if I'm wrong) if I create an external cell reference in
a cell using INDIRECT()/ADDRESS() functions, I *DO* need the file open in
order for the values to be updated/recalculated.

Is there any way around this?

I want to create the external cell reference using INDIRECT (other cells on
the worksheet will have the file name, sheet name, columns & rows as their
values) *WITHOUT* having to open the other files in order to get the cells
to recalculate/update.

Thanks for any help anyone can provide,

Conan Kelly

 

[Harlan Grove]

Conan Kelly wrote...

If I put an external cell reference (a reference to a cell in another file)
in a cell, I do not need that workbook open in order for that value to be
updated.

True, but such references are evaluated as either scalars (single
values) or arrays, not as range references. This is a subtle but
crucial difference. Also, the syntax of external references makes them
hard-coded, which allows Excel to cache values from external reference
links.

But (and correct me if I'm wrong) if I create an external cell reference in
a cell using INDIRECT()/ADDRESS() functions, I *DO* need the file open in
order for the values to be updated/recalculated.

Correct because INDIRECT can *only* return range references, and ranges
can only exist (in the technical sense) in *open* workbooks.

Tangent: there's NEVER any need to use ADDRESS within INDIRECT.

Is there any way around this?

I want to create the external cell reference using INDIRECT (other cells on
the worksheet will have the file name, sheet name, columns & rows as their
values) *WITHOUT* having to open the other files in order to get the cells
to recalculate/update.

There's *NO* way to do this with built-in functionality. You either
need to construct static external references or use add-on functions.
See the following article in the ng archives.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://preview.tinyurl.com/sl5nn )