How to Index Match from multiple closed workbooks in excel.

[Wadoodoo]

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. 

[#2]

So Ideally...each column represents a month and the excel could go on and on however you do bring up a valid point about the performance...could you do it till column CCC?

[#3]

@Wadoodoo 

That is still more than 2100 columns, i.e. over 175 years!

By the way, in which row or rows do you want the formulas?

[#4]

Hmm that makes sense its way to much...I think till UZ would be fine for the columns and for the rows we could do it till row 500?

[#5]

@Wadoodoo 

Starting in which row? I need exact information!

[#6]

Starting from row 7

[#7]

To be absolutely precise, from G7 to UZ500 (G7:UZ500)

[#8 · Accepted 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.

[#9]

WOWWWW!!!! it works....now if i want to implement the code into my existing excel do i just copy and paste the code into VBA into my existing excel?

[#10]

@Wadoodoo 

Assuming that the layout of your worksheet is the same as that of my sample worksheet, yes - copy the code as I described a few replies ago.

[#11]

Ok so I referred to my sheet and the values you put in columns A, B, C is actually in C, D and E. Do I just change the code and put C in place of A , D in place of B and E in place of C?

[#12]

@Wadoodoo 

Yes: for example, "A7:C500" becomes "C7:E500".

[#13]

Sorry i meant to say D, E and F (i.e replace A with D, B with E, and C with F.

[#14]

Hi Hans....It has finally worked really appreciate all your help and guidance. Thank you so much.

[#15]

Hey, I'm trying to do the same thing but I don't need the match function. This file is a lot of help and since I don't need any lookup value, I edited the code. But this VBA code isn't showing as a macro in the file and also, even this code without any changes, I'm not able to run. I would appreciate it if you can help me out

[#16]

@Shubhi1198 

The VBA code in the workbook is not a macro,so it doesn't show up in the Macros dialog.

It is a so-called event procedure that will be run automatically by Excel when you change the value of a cell in a specific range (A7:C500 in the sample workbook). The code belongs in the worksheet module, not in a standard module.

If you wish, you can attach a copy of your workbook to a reply, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar.

[#17]

@Hans Vogelaar  Thanks for the reply. I have no knowledge about VBA or event procedures.

@Link to file 

I have attached the file for your reference. Can you please have a look at the code?

I'm trying to pull data from multiple workbooks and multiple worksheets by providing path, workbook name, and worksheet name. From every worksheet, I need to pull data from a specific cell which is C5. I would appreciate it if you can look at the issue. There can be some errors in the code that I might not be able to identify. Also, it would be helpful if I can get it as a macro code.

Thanks for the help in advance. Let me know in case of any queries.

[#18]

@Shubhi1198 

Here is the code as a macro.

Private Sub Get_Data()
    Dim rng As Range
    Dim r As Long
    Dim m As Long
    Dim s As String
    Dim f As String
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    m = Range("A" & Rows.Count).End(xlUp).Row
    Range("G7:G" & m).ClearContents
    For r = 7 To m
        s = Range("A" & r).Value & "\" & Range("B" & r).Value
        If Dir(s) <> "" Then
            s = "'" & Range("A" & r).Value & "\[" & Range("B" & r).Value & "]" & Range("C" & r).Value & "'!C5"
            f = "=IFERROR(" & s & ","""")"
            On Error Resume Next
            Range("G" & r).Formula = f
            On Error GoTo ErrHandler
        End If
    Next r
ExitHandler:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[#19]

@Hans Vogelaar 

Thank you so much for the help, Hans. This code worked and I'm able to extract the data from the workbooks.

Can you please help me modify the code a little bit? I'm using 7-8 workbooks and every workbook has 15-20 worksheets. So, I want to use different categories for every workbook and want to use a single cell for providing the workbook name for every category (can select them using data validation list). And for the file path, just want to have a single cell. I have attached the file for your reference. It would save me a lot of time since I have to update the file path and workbook name on a weekly basis.

Thanks for the help in advance :). If can't be done, will use it this long way only.

[#20]

@Shubhi1198 

See if this works for you:

Sub Get_Data()
    Dim r As Long    ' row number
    Dim m As Long    ' last row
    Dim p As String  ' path
    Dim b As String  ' filename
    Dim s As String  ' cell reference
    Dim f As String  ' Formula
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    p = Range("B6").Value
    m = Range("A" & Rows.Count).End(xlUp).Row
    Range("G7:G" & m).ClearContents
    For r = 7 To m
        If Range("A" & r).Value <> "" Then
            If Range("C" & r).Value <> "" Then
                b = Range("C" & r).Value
            Else
                s = p & "\" & b
                If Dir(s) <> "" Then
                    s = "'" & p & "\[" & b & "]" & Range("D" & r).Value & "'!C5"
                    f = "=IFERROR(" & s & ","""")"
                    On Error Resume Next
                    Range("G" & r).Formula = f
                    On Error GoTo ErrHandler
                End If
            End If
        End If
    Next r
ExitHandler:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[#21]

@Hans Vogelaar 

Thanks a lot for your patience and help on this. This code is working very well and I'm also able to execute it as a macro. Really grateful to you for all of your help on this code :)