Variables in Match

[The Phil]

Since this spans over multiple files it would be hard to upload an attachment so bear with me as I try to explain it.

I made a formula that would take the left 4 digits of cell A4 and search for them in the column of another workbook. The formula also takes the right 2 digits and searches for them in a row in the other workbook. Using the DIRECT and ADDRESS functions, I use the values returned by the match (offset by 10 and 3 because the ranges don't start at 1) to get the value where they intersect on the other workbook. This formula works fine:

=INDIRECT("'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!"&ADDRESS(MATCH(LEFT(A4,4),'[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$B$11:$B$57)+10,MATCH(RIGHT(A4,2),'[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$D$5:$K$5)+3))

Then I thought that it would be good to use variables, because the location and name of the other file might change. I created a sheet called "Variables" and I put the name of the other file in B2, the directory in B3, and the sheet name of the other file in B4. I used INDIRECT.EXT to do the following, which also works just fine:

=INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]"&Variables!B4&"'!"&ADDRESS(MATCH(LEFT(A4,4),'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$B$11:$B$57)+10,MATCH(RIGHT(A4,2),'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$D$5:$K$5)+3))

But as you can see, the MATCH function still contains specific information. I can't figure out a way to make it refer to a variable path like what I did with the first part of the formula. Is it possible for MATCH to look in a variable location? In other words, I want it to look in a range in file "N", where "N" is the filename specified by another cell (in this case Variable!B2).

I hope that makes sense.

Thanks

[The Phil]

Is my question unclear or is it impossible?

[6StringJazzer]

Looks like you have woven yourself a complicated web, but can't you just do this:


=INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]"&Variables!B4&"'!"&ADDRESS(MATCH(LEFT(A4,4),'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$B$11:$B$57)+10,MATCH(RIGHT(A4,2),INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]Facilities'!$D$5:$K$5"))+3))

I didn't set up your whole situation so I can't guarantee this works. I also don't know how INDIRECT.EXT works. Is it an add-in?

[teylyn]

Jazzer, Indirect.Ext is included in morefunc.xll

It can perform the Indirect on closed workbooks.

[The Phil]

Looks like you have woven yourself a complicated web, but can't you just do this:


=INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]"&Variables!B4&"'!"&ADDRESS(MATCH(LEFT(A4,4),'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$B$11:$B$57)+10,MATCH(RIGHT(A4,2),INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]Facilities'!$D$5:$K$5"))+3))

I didn't set up your whole situation so I can't guarantee this works. I also don't know how INDIRECT.EXT works. Is it an add-in?

Simple, and brilliant. I just tested it by trying it on the MATCH part of the formula and it worked!!! Don't know why I didn't think of that LOL.

Thanks so much, now I'm gonna have to string it together, and I'm gonna make the directory, filename, sheet name, and cell range all variable so hopefully it works out.