Help using INDIRECT / INDIRECT.EXT with paths created by FUNCTIONS

[Guest]

Hi,

I have this INDIRECT>EXT function to retrieve ONE value from one cell on
another workbook:

=INDIRECT.EXT(CONCATENATE("'T:\Excel\ABM Amro
Expenses\Expenses\TCF\20040816\[ExportXXXX.xls]Export'!",ADDRESS(I13+7,I14,TRUE)),TRUE)

Now the name and location of the source book is generated like this:

Root = T:\Excel\ABM Amro Expenses\Expenses\TCF\
Date = 20040816
Filename = "Export" AND Fundname (XXXX) AND ".xls"
The source worksheet will ALWAYS be "Export"

When I have tha path and filename hardcoded like that it works fine, but I
need it to be derived, as the date folder will change every day, and I have
to enter this formula for 100 different funds to retrieve data using formula
every business day.

I have tried everything I can think of to concatenate the root with a
derived formatted string from the date then the workbook name and the sheet,
but no luck.

Can anyone help me or show me where I am going wrong?

thanks

Philip

 

[Frank Kabel]

Hi Philip
Harlan responded to your question in your other thread. As
Indirect.ext returns only the upper left element of your
specified range for a closed workbook you can't use
Indirect.ext as second parameter in your MATCH function.
You won't get your results.
So you may take a look at the other alternatives provided
in the original link (e.g. SQL.Request or Harlan's Pull
function).

Though I would assume they would be too slow for your
requirements (daling with several closed workbooks,
looking through large ranges)

Frank

P.S.: please stay in the opriginal threa, no reason for
posting this question again and again

 

[hgrove]

Frank Kabel wrote...
...

Though I would assume they would be too slow for your
requirements (daling with several closed workbooks, looking
through large ranges)

...

Looking through many closed files would be slow, but range size woul
matter less. Besides, if the alternative is gathering the dat
manually, several hours of computer times still beats a few days o
human time, no?

FWIW, I collect data from remote 123 files each quarter. It require
overnight runtime, but it's much better than manually collecting it.

SQL.REQUEST and pull don't have to be blink-of-an-eye fast, just faste
than any other alternative

 

[Frank Kabel]

..

..

Looking through many closed files would be slow, but range size would
matter less. Besides, if the alternative is gathering the data
manually, several hours of computer times still beats a few days of
human time, no?

lol, yes indeed. Still thought also the number of cells you lookup in
each indicidual file would also increase the runtime. I would have
expected a linear dependency (as for example your pull function calls
the Excel4 Macro for each cell - if I remember the function correctly).

But you're right: If there's no chance to change the Excel file base
data structure to something different you'll have to live with these
(slower) alternatives.

Frank

 

[hgrove]

Frank Kabel wrote...

. . . Still thought also the number of cells you lookup in each
indicidual file would also increase the runtime. I would have
expected a linear dependency (as for example your pull function
calls the Excel4 Macro for each cell - if I remember the function
correctly).

...

It does, but creating and destroying the additional applicatio
instance adds appreciable overhead that flattens the dependency