Feb 12 2018
- last edited on
Jul 25 2018
I want to use the formula Index to get a value from an external excel file.
I have one cell where I use Concatenate to create the reference to be used in Index.
For exemple :
A1 = "C:/MyFile/" wich is the location of the file
C1 = Concatenate("'",A1,"[",B1,"]Tab1'!$A$1:$G$20"
Result is 'C:/MyFile/[Worksheet.xlsx]Tab1'!$A$1:$G$20
Then I want to use in D1 :
Index(C1,4,2) for getting the value in the 4th line and the 2nd column of the range A1:G20 in the Tab1 of Worksheet in C:/Myfile/
The problem is that in the Index Formula, A3 comes as "'C:/MyFile/[Worksheet.xlsx]Tab1'!$A$1:$G$20"
With double quote at the beginning and at the end which is not a reference
I can't use INDIRECT as the external file is not open.
The idea is to have multiple line with different value for the path and the file name and always have the same formula in column D, E, F, ... that get different cells from the external files and I don't want to open all these files before having my "projects summary".
How can I use the concatenate reference in the Index formula.
Feb 13 2018 02:14 AM - edited Feb 14 2018 06:45 AM
A bit of VBA code can do this.
Sub ResolveIndexForCells() Dim oCell As Range For Each oCell In Range("C1:C" & _
Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row) oCell.Offset(, 1).Formula = "=Index(" & oCell.Value & ",4,2)" oCell.Offset(, 1).Value = oCell.Offset(, 1).Value Next End Sub
Feb 13 2018 12:54 PM
Thank you very much Jan,
Finally found that there it was missing the .Row property at the end of the For Each line.
Sep 15 2021 10:41 PM
@Jan Karel Pieterse Thanks so much for this! This was very helpful for writing my own VBA external reference workaround!
Completely new to VBA so this may be clunky, but in case it might help anyone else:
Sub LookupReference() ' Define all variables: loop var, loop end count, INDEX() path+reference, MATCH() path+reference, column number for INDEX() Dim i As Integer Dim NumRows As Integer Dim IndexRef As String Dim MatchRef As String Dim ColumnNum As Integer Application.ScreenUpdating = False ' Set NumRows to number of rows of data from C4 down, stopping at first blank NumRows = Range("C4", Range("C4").End(xlDown)).Rows.Count ' Set IndexRef to the string in cell J6 IndexRef = Range("J6").Value ' Set MatchRef to the string in cell J7 MatchRef = Range("J7").Value ' Select cell C4 Range("C4").Select ' Establish For Loop to loop "NumRows" number of times For i = 1 To NumRows ' Set ColumnNum to the value in Column F during each loop ColumnNum = ActiveCell.Offset(0, 3).Value ' Set =INDEX(IndexRef,MATCH($B$1,MatchRef,0),ColumnNum) as formula ActiveCell.Formula = "=INDEX(" & IndexRef & ",MATCH($B$1," & MatchRef & ",0)," & ColumnNum & ")" ' Select cell down 1 row from active cell, iterate loop ActiveCell.Offset(1, 0).Select Next Application.ScreenUpdating = True End Sub