Using INDEX/MATCH with indirect

[jdpjtp910]

Ok,
I have two workbooks. For this example we will call one workbook data.xls and the other is results.xls. The data.xls has 13 tabs (one for each month and total tab YTD). I want to use results to pull data from certain spots on data.xls.

Clear as mud yet?


I attached sample results on this thread.

I want to use a formula to pull data from the table based on index/match but want to use indirect. Here is my index function:


=INDEX([data.xls]Jun!$B$2:$D$4, MATCH(A2,[data.xls]Jun!$A$2:$A$4,0),MATCH(A1,[data.xls]Jun!$B$1:$D$1,0))

Now, I do not want data.xls AND/OR Jun to be static. I want to use something like this:

=INDEX(["&A3&".xls]Jun!$B$2:$D$4, MATCH(A2,["&A3&".xls]Jun!$A$2:$A$4,0),MATCH(A1,["&A3&".xls]Jun!$B$1:$D$1,0))

OR

=INDEX([data.xls]"&A$&"!$B$2:$D$4, MATCH(A2,[data.xls]"&A4&"!$A$2:$A$4,0),MATCH(A1,[data.xls]"A4&"!$B$1:$D$1,0))

SO my question is how do I use an indirect within my index/match???

I DO NOT want to use an indirect function that uses name ranges. I would need to name ranges constantly and would take away from my "automation" of this data entry spreadsheet. Anyone got any ideas? I will use an INDIRECT if I knew how to use indirect/match to find ranges of cells vs defined names...?

Thank you in advance to anyone who can help!

[contaminated]

Whatever, you won't be able to pull data from a closed workbook with native excel function, despite of using INDIRECT. You will have to use Harlan's PULL function or INDIRECT.EXT from morefunc.xll

[jdpjtp910]

It only needs to pull when open. Not concerned with it being closed or not. It will only be populated with a roll up so no need for it to work when closed.

[jdpjtp910]

Thank you guys!

[PelleCadol]

Hello,
I am having trouble using the formula stated above. I want to be able to do about the same as the thread's initiator, thus I have just copied the formula, and changed it to contain what I need.

=INDEX(INDIRECT("'["&B2&".xls]'!$D2:$D1000");MATCH(C5;INDIRECT("'["&B2&".xls]'!$A$1:$A$1000"));MATCH(I2;INDIRECT("'["&B2&".xls]'!$C$4:$IV$4")))

When I use this formula I get the error "#REF!". I do not understand what this error refers to in this instance. Have I misused the formula?
Best regards Pelle Jorgensen

[NBVC]

Ditto as per Conntaminated's comments...

The formula, if you keep workbook open is:

=INDEX(INDIRECT("'["&A3&".xls]Jun'!$B$2:$D$4"), MATCH(A2,INDIRECT("'["&A3&".xls]Jun'!$A$2:$A$4"),0),MATCH(A1,INDIRECT("'["&A3&".xls]Jun'!$B$1:$D$1"),0))

and if you will add the Morefunc free addin...

then replace INDIRECT in formula above with INDIRECT.EXT

[NBVC]

Welcome to the forum,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.