Forum Discussion
How to Index Match from multiple closed workbooks in excel.
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.
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.
- I don't think this worked....also what do I save the macro as?
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.
HansVogelaar 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?