Referencing table in another workbook

[jwburritt]

Hello: I have a formula in a CurrentSheet that is looping through data looking at the headers of a data table (data_table) in the same workbook for index and match. That code works fine and is below. However, I need to make it refer to another data table in another workbook for the data. That table is named the same and has the same header structure. I've tried a ton of different ideas to make the reference work, but so far no luck.

Any help would be appreciated!

For i = 1 To 7    
    With Cells(i, 7)
                .Formula = "=INDEX(data_table[Account Type],MATCH(CurrentSheet!" & Cells(i, 6).Address(0, 0) & ",data_table[Account],0))"
                .Value = .Value
    End With
                
    Next i

 

[jwburritt]

Any help with this would be appreciated. I'm still stuck.

 

[Tetra201]

Maybe this (untested):

[COLOR=#ff0000]SrcName = "Other Workbook.xlsx"[/COLOR]
For i = 1 To 7
    With Cells(i, 7)
        .Formula = "=INDEX([COLOR=#ff0000]'" & SrcName & "'![/COLOR]data_table[Account Type],MATCH(CurrentSheet!" & Cells(i, 6).Address(0, 0) & ",[COLOR=#ff0000]'" & SrcName & "'![/COLOR]data_table[Account],0))"
        .Value = .Value
    End With
Next i

 

[jwburritt]

Thank you, but that didn't work: syntax error.

 

[Tetra201]

The suggested solution has now been tested to work as posted.
Note: the "Other Workbook" should be opened in the same instance of Excel.

 

[jwburritt]

Thanks again. This syntax ended up working...

.Formula = "=INDEX('[Other Workbook.xlsm]Data'!data_table[Account Type],MATCH(CurrentSheet!" & Cells(i, 6) _
                    .Address(0, 0) & ",'[Other Workbook.xlsm]Data'!data_table[Account],0))"