Aug 24 2021 09:56 AM
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.
Aug 24 2021 01:30 PM
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...
Aug 24 2021 01:33 PM
@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?
Aug 24 2021 02:09 PM
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.
Aug 24 2021 02:17 PM
Aug 24 2021 02:21 PM
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.
Aug 25 2021 04:13 PM
@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?
Aug 25 2021 04:55 PM
Aug 26 2021 12:40 AM
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.
Aug 26 2021 08:51 AM
Aug 26 2021 09:04 AM
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).
Aug 26 2021 09:10 AM
Aug 26 2021 09:24 AM
Aug 26 2021 09:25 AM
Aug 26 2021 09:32 AM
Aug 26 2021 09:55 AM
Thanks. In which columns do you want such formulas?
Aug 26 2021 09:57 AM
Aug 26 2021 10:05 AM
@Wadoodoo Until which column?
Aug 26 2021 10:07 AM
Aug 26 2021 10:09 AM
Do you really have thousands and thousands of columns with data? That would be a disaster, performance-wise...
Aug 26 2021 11:07 AM
SolutionHere 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.