Excel External Reference Formula

Copper Contributor

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

4 Replies

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

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

Hmm, Sorry about the omission! I'll edit the post if I can.

@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