Refer to Excel workbook by path, based on cell value data

Question

I have an Excel sheet that draws data from other, closed Excel workbooks. Currently it works fine when I list out the closed workbook's entire path, but I'd like to use a variable, stored in a separate cell, as part of the path name.

For example, I am trying to reference a workbook called

workbook12.10.12.xls

In a separate workbook (we'll say the "active" workbook), I have a cell with formula

=INDEX('C:\Path[workbook12.10.12.xls]SHEET1'!$B$1:$B$5, MATCH("match text", 'C:\Path[workbook12.10.12.xls]SHEET1'!$A$1:$A$5, 0))

which finds the value in workbook12.10.12's B column corresponding to the cell in the A column that contains "match text." This works fine; however, I have a cell in the active workbook with the value

12.10.12

and would like to somehow reference this value in the INDEX function.

I can't have the other workbooks open, so the INDIRECT function won't help. Googling seems to suggest that Excel doesn't have a simple one-stop solution for this kind of thing... can someone help please? Thanks!

Answer 1

From Frank Kabel's 2004 post at Dicks Blog you could

1. Use Laurent Longre has developed the free add-in MOREFUNC.XLL which includes the function INDIRECT.EXT
2. Use SQL.REQUEST as described here *does not appear to be supported anymore and I am not clear if this could handle your INDEX\MATCH request
3. Use Harlan Grove’s PULL function

In addition you could:

1. Create a "dirty link" directly via code that enters a formula referring to the workbook you need
2. For pulling values - but not for working with ranges - you could use Walkenbach's ExecuteExcel4Macro XLM method

Answer 2

I think what you what to do is to find the specific record in the specific file (date named). You may do it by a simple VBA code.

Suppose you are going to search for a record# say REC001 in A1, date file 12.10.12 at cell C1, and have the result to be display at cell A7

On the worksheet you want to enter input and get output, rightclick the sheet tab and select 'View code' and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
Range("A7").Formula = "=INDEX('C:\TEMP\[workbook" & Range("C5").Value & ".xls]SHEET1'!$B$1:$B$5, MATCH(" & Range("A1").Value & ", 'C:\TEMP\[workbook" & Range("C5").Value & ".xls]SHEET1'!$A$1:$A$5, 0))"
End Sub

Then every time you edit C1, the formula will be updated.

Actually I don't think you should use INDEX function in your case. It is more simple to use a VLOOKUP. E.g.:

Range("A8").Formula = "=vlookup(" & Range("A1").Value & ",'C:\TEMP\[workbook" & Range("C5").Value & ".xls]SHEET1'!$A$1:$B$5,2,false)"

You will have to note on a few points: 1. you paste the code on the Sheet1 object (or the sheet name) but not to insert a new module 2. your path and filename for the target file is correct, including the .xls and .xlsx 3. your original file only cover to $B$5 4. on VBA, recommend you to save the file as .xlsm format

Answer 3

You can store a full reference including the file path to a range in a closed file in a name in excel (either directly or via VBA based on selections in different cells and using the Worksheet_Change procedure as above) and then refer to the file using the name in a formula as normal. This gets over the limitation in the INDIRECT function.

The VBA is very simple:

New_Ref = Sheets("Wells").Range("K6")

ActiveWorkbook.Names("MyWorkbook").RefersTo = "=" & New_Ref

The only trick is to be sure to include "=" in the name.

Names have a huge number of uses once you spot this. I have used this to get data from a closed file on a remote sharepoint site without any difficulty - I assume sharepoint deals with all the permissions.