INDEX Match with a referenced file path to a closed file

[e8stewie]

Hello,
I'm looking to reference an array in a closed workbook as a part of an index match function. I've got the following hard coded function to work:

=INDEX('C:\Users\owner\Documents\1.1.1\[CAP 1.1.1-REPLAN.xlsx]CAP'!$1:$1048576,MATCH(E5,'C:\Users\owner\Documents\1.1.1\[CAP 1.1.1-REPLAN.xlsx]CAP'!$5:$5,0),6)

I want to have the file paths (arrays) be dynamic and look at a cell. For example:

=INDEX(A6,MATCH("value to lookup",A6,0),6)

Though despite all my best efforts (I consider myself advanced) I cannot seem to get the reference to work. I seems like there is an extra set of quotes that are being inserted that are throwing it off. I.e., the true text is being lost and therefore the file path.

How can I format the reference cell (i.e., A6) to appear as an array for the INDEX MATCH combo? Perhaps the solution lies in formatting the array part of INDEX (I've tried CELL, ADDRESS, INDIRECT).

I thank you in advance. The users here are amazing and I look forward to your response. Please let me know if I'm being unclear or if you have any questions.

 

[konew1]

What is actually in cell A6?
is it a cell range like A1:K99 or a word that is the name of a named data range? or something entirely different?

 

[hrlngrv]

INDIRECT won't work with closed workbooks.

The hardcoded formula, which I'll paraphrase as

=INDEX('C:\...\[fn]CAP'!$1:$1048576,MATCH(E5,'C:\...\[fn]CAP'!$5:$5,0),6)

looks suspicious. The 1st reference is to the entire CAP worksheet, which is probably excessive. The 2nd reference is to all of <b>row</b> 5 in the CAP worksheet. So you're looking for the value of the local worksheet's E5 cell in row 5 of the CAP worksheet, thus returning the <b>column</b> index. The MATCH call could only return 1 to 16384, so the 1st reference could be reduced to 'C:\...\[fn]CAP'!$1:$16384.

That said, to repeat: INDIRECT doesn't work with closed workbooks. Given your unusual addressing, your only alternatives are Laurent Longre's MOREFUNC.XLL add-in, specifically its INDIRECT.EXT function, or my pull udf, which uses a 2nd Excel application instance to evaluate references into closed workbooks.

MOREFUNC.XLL is available at

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

My pull udf as a .BAS file (import into a VB Project as a module) is available at

http://www.4shared.com/file/L_eA8s4G/pull.html

Using MOREFUNC, you'd be better off constructing cell references, e.g.,

=INDIRECT.EXT("'"&drive_directory_path&"["&file_name&"]"&worksheet_name&"'!R"&
MATCH(E5,INDIRECT.EXT("'"&drive_directory_path&"["&file_name&"]"&worksheet_name&
"'!5:5"),0)&"C6",,1)

where the inner INDIRECT.EXT call is in A1 referencing syntax and the outer call is in R1C1 syntax.

 

[e8stewie]

What is actually in cell A6?
is it a cell range like A1:K99 or a word that is the name of a named data range? or something entirely different?

Konew,
Cell A6 would house the file path in in the array. For example,
C:\Users\owner\Documents\1.1.1\CAP 1.1.1-REPLAN.xlsx

Cell a7 would house C:\Users\owner\Documents\1.1.2\CAP 1.1.2-REPLAN.xlsx

My intention is to have a host of files that I need to check for certain data in. The A column would house each file path. I'm getting these from a macro that uses the file picker to place the file name in a cell. I do realize I'd need to clean it up to get it to match the way it looks when excel converts it, but that can be done with a formula in the B column (so technically the reference would be in the B column).

Thanks.

 

[e8stewie]

INDIRECT won't work with closed workbooks.

The hardcoded formula, which I'll paraphrase as

=INDEX('C:\...\[fn]CAP'!$1:$1048576,MATCH(E5,'C:\...\[fn]CAP'!$5:$5,0),6)

looks suspicious. The 1st reference is to the entire CAP worksheet, which is probably excessive. The 2nd reference is to all of row 5 in the CAP worksheet. So you're looking for the value of the local worksheet's E5 cell in row 5 of the CAP worksheet, thus returning the column index. The MATCH call could only return 1 to 16384, so the 1st reference could be reduced to 'C:\...\[fn]CAP'!$1:$16384.

That said, to repeat: INDIRECT doesn't work with closed workbooks. Given your unusual addressing, your only alternatives are Laurent Longre's MOREFUNC.XLL add-in, specifically its INDIRECT.EXT function, or my pull udf, which uses a 2nd Excel application instance to evaluate references into closed workbooks.

MOREFUNC.XLL is available at

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

My pull udf as a .BAS file (import into a VB Project as a module) is available at

http://www.4shared.com/file/L_eA8s4G/pull.html

Using MOREFUNC, you'd be better off constructing cell references, e.g.,

=INDIRECT.EXT("'"&drive_directory_path&"["&file_name&"]"&worksheet_name&"'!R"&
MATCH(E5,INDIRECT.EXT("'"&drive_directory_path&"["&file_name&"]"&worksheet_name&
"'!5:5"),0)&"C6",,1)

where the inner INDIRECT.EXT call is in A1 referencing syntax and the outer call is in R1C1 syntax.

Thanks hrlngrv,
I'll give those a shot, but is it true that each user would have to have the add-in installed in order for it to work outside my machine? I'm trying to keep this free of macros and or add-inds for that reason. That being said, I need to get the job done.

Would the OFFSET function work here as it defines an array? Thanks.

 

[hrlngrv]

. . . is it true that each user would have to have the add-in installed in order for it to work outside my machine? I'm trying to keep this free of macros and or add-inds for that reason. That being said, I need to get the job done.

Would the OFFSET function work here as it defines an array? Thanks.

No built-in Excel functions can give you dynamic references into closed workbooks. Not INDIRECT, not OFFSET. In technical terms, INDIRECT and OFFSET only return Range objects in <b>open</b> workbooks. Feed either a reference into a closed workbook, and it'll return #REF! errors.

The only alternative is when there are only a small number of closed workbooks you'd need to reference, all with full pathnames known ahead of time. For example, the workbooks were always in C:\x\y\z and had file names for each month of the year, so January.xlsx, February.xlsx, etc. Then you could define names like

JanuaryData: ='C:\x\y\z\[January.xlsx]particular worksheet'!$1:$16384
FebruaryData: ='C:\x\y\z\[February.xlsx]particular worksheet'!$1:$16384
:
DecemberData: ='C:\x\y\z\[December.xlsx]particular worksheet'!$1:$16384

SelectedData: =CHOOSE(month_number,JanuaryData,FebruaryData,...,DecemberData)

where month_number is either a name or an expression which gives the month number you're seeking. Then you could use formulas like

=INDEX(SelectedData,MATCH(E5,INDEX(SelectedData,5,0),0),6)

which would ultimately refer to static references into one of the alternative workbooks based on the static reference month_number produces with the CHOOSE function.

If you must accommodate any valid filename, you have no alternatives other than Longre's MOREFUNC.XLL add-in or my pull udf. This is just how Excel works, or rather doesn't.