SOLVED

How to Index Match from multiple closed workbooks in excel.

Copper Contributor

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 PathB1 - File NameC1 - Sheet NameD1 - Concatenated String FormulaE1 -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. 

46 Replies

@Wadoodoo 

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...

@Hans Vogelaar 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?

@Wadoodoo 

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 Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.

I don't think this worked....also what do I save the macro as?

@Wadoodoo 

After creating the code as described in my previous reply and switching back to Excel, press F12 to activate the Save As dialog.

Select 'Excel Macro-Enabled Workbook (*,xlsm)' from the 'Save as type' drop-down.

Click Save.

@Hans Vogelaar ok so I converted the file and put in the code you shared under view code. It then asks me to create a macro. Once I create the new macro it opens a new clear window...What do I do from here?

Also when I run the code there is an issue that comes up which says object required.

@Wadoodoo 

I'm not sure what exactly you have done. Here is a sample workbook. Edit G4 to a value you expect to work, then edit A1, B1 or C1, or try entering data in the rows below.

It worked when I tested it with paths/workbooks on my computer.

Hi Hans,
First of all thank you so much for your patience in helping me with this problem I really appreciate all the time and effort you have put in. I downloaded the file you posted and noticed that in G4 in G4 all I see is Columbus...I do not see any formula or macro you may have installed. I have never used VBA before and was wondering if you could give me a step by step approach on how to implement the code in my existing excel. Look forward to your guidance and advice.

@Wadoodoo 

The value Columbus in G4 was  just a dummy value that I used to test the code.

If you want to view the code, right-click the sheet tab and select 'View Code' from the context menu. You can then copy the code, switch to your own workbook, select 'View Code' from the context menu of the sheet tab and paste.

Don't forget to save your workbook as a macro-enabled workbook (.xlsm).

Hi Hans,
Thank you for the prompt response. I have already added the code to my macro enabled worksheet however the values are not being generated in excel. I also want to confirm that I plan to use this across multiple columns not just G4 is the code scalable for all columns in the workbook?
Hi Hans,

Another question I had is did you put any code in the set of cells under column G or was the workbook able to pick out the data automatically? Thank you so much for your guidance and advice.

@Wadoodoo 

 

Can you explain in more detail what you want to do? 

@hans

Absolutely...so I am essentially dealing with a cashflow statement where each line item from the cash flow statement is taken out from a separate workbook. The data I am trying to match it against is the date which is the same across all workbooks. I want excel to be able to do this while the workbooks are closed.

Currently I have hard coded the file path within the index match syntax like so:

=IFERROR(INDEX('C:\Users\harsh\Desktop\Cash Flow Tools\Line Items\[Discounts given.xlsx]Discounts given'!$C:$C,MATCH(G$5,'C:\Users\harsh\Desktop\Cash Flow Tools\Line Items\[Discounts given.xlsx]Discounts given'!$B:$B,0)),0)

Like this there are 50 other line items (i.e workbooks) which I have hardcoded into my excel. This however is not a scalable solution since these sheets are updated on a monthly/bi-weekly basis (depending on the line item).

What I want to do is set up a system where I just need to change the name of the workbook and sheet in two different cells and the workbook will be able to automatically update without having to manually change the code and define the file path across every single row.

I hope this makes sense and please do advise if you need any further clarification.

Thank You.

@Wadoodoo 

Thanks. In which columns do you want such formulas?

From G onwards...However could you explain to me how you call the macro to run across these columns?
I would like it to continue indefinitely. Otherwise till XFD.

@Wadoodoo 

Do you really have thousands and thousands of columns with data? That would be a disaster, performance-wise...

1 best response

Accepted Solutions
best response confirmed by Wadoodoo (Copper Contributor)
Solution

@Wadoodoo 

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.

View solution in original post