Feb 12 2018
02:17 PM
- last edited on
Jul 25 2018
11:02 AM
by
TechCommunityAP
Feb 12 2018
02:17 PM
- last edited on
Jul 25 2018
11:02 AM
by
TechCommunityAP
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
B1= "Worksheet.xlsx"
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.
Thanks
Pierre
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.
Fantastic result.
Thanks again
Pierre
Feb 14 2018 06:44 AM
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