Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External cell references using INDIRECT & ADDRESS
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External cell references using INDIRECT & ADDRESS
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/micro...443753560f0075 (or http://preview.tinyurl.com/sl5nn ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - copy absolute cell references (within the range) as relati | Excel Discussion (Misc queries) | |||
Active Cell To Change Formula | Excel Discussion (Misc queries) | |||
Replace a spreadsheets named cells/ranges with exact cell address. | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Syntax for inferred cell references | Excel Worksheet Functions |