Forum Discussion
How to Index Match from multiple closed workbooks in excel.
Dear Tech Community,
I am trying to reference multiple closed Excel workbooks with different file paths and file names. My initial approach was to define the file path and file name in different cells and concatenate it into the string with the relevant syntax (i.e putting square brackets around the file name, putting an exclamation point after the sheet etc.]
| A1 - File Path | B1 - File Name | C1 - Sheet Name | D1 - Concatenated String Formula | E1 -Index Match Formula |
| C:\Users\wadoodoo\Desktop\Cash Flow Tools\Money\ | Sample Excel.xlsx | Sample Sheet | =CONCATENATE("'" & A1 & "[" & B1 & "]" & C1& "'") | =INDEX('D1'!$C:$C,MATCH(G5,'D1'!$B:$B,0)) |
Unfortunately this does not work. Every time I have to manually select the file which is really tedious as I have to do this across 50 different files and each row refers to a different closed workbook.
Is there any way I can make this process a bit more dynamic since all I want to do is change the file name and excel should be able to pick up all the relevant data from the new file automatically. Each file will have a new version on a bi-weekly/monthly basis.
Look forward to any guidance and advice. Thank you in advance.
Here is a new sample workbook.
The current values in G5:UU5 are just dummy values.
First, enter some valid lookup values in G5, H5, etc. You don't have to do all of them, just a few for now.
Next, correct the values in A7, B7 and C7, or if they are already correct, select one of them, press F2, then press Enter.
Then look at G7, H7, etc. Hopefully, they will now contain formulas.
If that works, add data for a different workbook in A8 to C8 and then look at G8, H8 etc.
46 Replies
You'd need the INDIRECT function for this, but unfortunately this only works with references to other workbooks if they are open, not if they are closed...
HansVogelaar thank you for the prompt response and help. I have used the INDIRECT function already and have a worksheet linked with all the other worksheets using INDIRECT...I dont mind using some VBA code to get this done but I have no idea how to start. If you do know some VBA could you please guide me as to which code could execute this task?
Try this:
Right-click the sheet tab.
Select View Code from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim r As Long Dim s As String Dim f As String On Error GoTo ErrHandler If Not Intersect(Range("A1:C50"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each rng In Intersect(Range("A1:C50"), Target).Rows r = rng.Row s = "'" & Range("A" & r).Value & "[" & Range("B" & r).Value & "]" & Range("C" & r).Value & "'!" f = "=INDEX(" & s & "$C:$C,MATCH(G4," & s & "$B:$B,0))" Range("E" & r).Formula = f Next rng End If ExitHandler: Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub ErrHandler: MsgBox Err.Description, vbExclamation Resume ExitHandler End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.
