Excel 2007 : INDIRECT, ADDRESS and an external spreadsheet

**samtoucan** · 12-12-2009, 01:42 AM

Hi All
(MODERATORS sorry corrected my formula and now the title post is wrong! no longer using ADDRESS)

1st up, Thank You for all the help in the past. Here's another problem

Firstly I dont even know if this is possible - Help was not much help, and I can't find a similar post.

Is it possible to modify the INDIRECT function to include the path and file name?

I have this formula
=LOOKUP(9.99999999999999E+307,INDIRECT("'"&$B203&"'!B2:B"&ROWS(B$1:B203)))
I need it to return a value from a cell in another spreadsheet. Lets say the directory is
"d:\test stuff"
and the file is
Slope.xlsx

Cheers

Sam

**JeanRage** · 12-12-2009, 03:28 AM

Hi,

If you dealing with a closed file, you will need to use the UDF called PULL() designed by Harlan Grove ...

HTH

**DonkeyOte** · 12-12-2009, 03:51 AM

You can also investigate morefunc.xll c/o Laurent Longre and specifically the INDIRECT.EXT function.

There was a thread opened in the Development Forum a week or so ago by someone with an add-in related to this matter - I can't say I've looked at it but the OP claims it's quicker than both PULL and the above - whether this true or not I have no idea but it may also be worth further investigation on your part ?

**samtoucan** · 12-12-2009, 07:16 AM

Not familiar with modules at all so may be making some fundamental errors.
Also please attached.

In my Analysis spreadsheet, which 'calls' the data spreadsheet called 'Slope'...
OK ALT+F11 to open VBA. Right clicked Modules, and Imported Module.
no probs so far
Saved as a XLSM file (which permit macros).

Now to use the module... I changed INDIRECT to INDIRECTEX, and added the path to the Slope file.
=LOOKUP(9.99999999999999E+307,INDIRECTEX("'D:\!Sam\A_Projects\Forex\[slope.xlsx]!"&$B203&"'!B2:B"&ROWS(B$1:B203)))
but it returns #value!

Note the formula works correctly when the data is in the Analysis spreadsheet.

Am I doing something fundamentally wrong, like path name not enclosed in quotes or something? Doco on INDIRECTEX appears to be scarce.
Will also try the PULL function on Monday - more posts in the forum for that.

PS
I wanted to split the Analysis from the Data sheets because the Analysis component opened much faster without the semi-static Data sheets (they only need to be calculated once). Was hoping the Data sheets could be left closed (take a while to load).

Cheers

Sam