Aug 24 2021 09:56 AM
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
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
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: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.
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:32 AM
Aug 26 2021 09:57 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...