0

I'm using the Index(Match()) function to lookup a value from another closed workbook. The formula within cell B5 of the currently opened workbook is:

=INDEX('C:\Users\Downloads\[og.xlsx]Sheet1'!$B$1:$B$3,MATCH($A$5,'C:\Users\Downloads\[og.xlsx]Sheet1'!$A$1:$A$3,0))

where $A$5 stores the lookup value.

The path of source file C:\Users\Downloads\[og.xlsx]Sheet1 will change dynamically, and it is stored in the current workbook in cell C1 as text string without ' at either ends. I would like to have some help writing a user defined function (not sub!) in vba to replace the fomula as:

=INDEX(getValue($C$1,"$B$1:$B$3"),MATCH($A$5,getValue($C$1,"$A$1:$A$3"),0))

NOTE:

  1. The source file should not open at any point, so INDIRECT() won't work.
  2. Excel add-in is not preferred, so no INDIRECT.EXT().
  3. The getValue(path,range) function should also work when range refers to a single cell value. (i.e. =getValue($C$1,"$B$1") should work as a cell formula alone)
  4. I noticed the post Use file path from a predefined cell in a formula , which works fine for single cell value but not range when using index(match()). Also, I tried the following which returns #VALUE!:
    Function GetValue(Rpath As String, Rrange As String)
        Dim a, strRef As String
        a = range(Rrange).Address(, , xlR1C1)
        strRef = "'" & Rpath & "'!" & a
        GetValue = ExecuteExcel4Macro(strRef)
    End Function

Thanks in Advance!

J.wz
  • 13
  • 5
  • 1
    Do you have a question? You've posted requirements, but no code and no question – teylyn Jul 23 '20 at 00:56
  • my question is how to write such a function? so far i tried `ExecuteExcel4Macro`, but it only works in sub(), not function(). – J.wz Jul 23 '20 at 01:00
  • Post your code and explain where you are stuck. Why would you use ExcecuteExcel4Macro if you use VBA? – teylyn Jul 23 '20 at 01:02
  • [This seems relevant](https://stackoverflow.com/questions/29521245/a-function-within-a-function-in-vba). – BigBen Jul 23 '20 at 01:07
  • thx, but the fix only works for single-cell reference, not for the range which is used in the index match function. – J.wz Jul 23 '20 at 02:41

0 Answers0