Hi,

I am trying to do something that I thought should be simple. I am using a
MATCH function to extract data from a different file than the one I am
working in. Now this works great as long as I am always referencing the exact
same file, BUT the problem arises when I need to able to change the lookup
file depending on which file I want to look up in.

I am basically populating a list of 10 columns and 40 rows where every
single row will collect data from a different file. I have made my formula
general so that when I have the file name in one column I can just pick up
the file name and insert it into my formula using an INDIRECT function like
this: NDIRECT("'["&$B3&".xls]Sheet1'!$H:$H") and then reuse the exact same
formula on the next row with a new file name. Now this works great AS LONG AS
the file that I am referencing is open. My reasoning is that the indirect
formula doesnt just give the reference to the array, but it actually returns
the array values as well and therefore requires the file to be open.
Now I tried to replace the INDIRECT function with a TEXT function, but MATCH
doesnt seem to like that even though I know that the resulting text is
correct.

Now we are getting into where my basic problem is. I want to use MATCH to
extract date from these other files and I used INDIRECT to solve my complex
reference problem in the MATCH formula, but that doesnt allow me to extract
date from files that are not open. Is there any other formula that I may
substitute INDIRECT with to make my MATCH function work. My MATCH formula
looks like this: MATCH("Total for Set
(USD):";INDIRECT("'["&$B3&".xls]Sheet1'!$H:$H");0)

Now I would have an exact identical MATCH nested into an INDEX function to
find the value that will actually be at the intersection of the results from
the two different MATCH functions. My total formula looks like this:
=INDEX(INDIRECT("'["&$B3&".xls]Sheet1'!$A:$iv");MATCH("Total for Set
(USD):";INDIRECT("'["&$B3&".xls]Sheet1'!$H:$H");0);MATCH("Budget
"&I$2;INDIRECT("'["&$B3&".xls]Sheet1'!$2:$2");0))

Note that my version of Excel uses: ; to separate the different conditions
in the formulas and not , as that is the decimal indicator here in Norway.

Looking forward to any creative suggestions.

Thank you in advance,