Using VBA cell reference to create external reference to closed workbooks

Question

I am stuck with a problem. i have a range of cells that contain the paths to external work books, all of which contain a same named range. E.g. in a workbook in cell B2, i have a text string reading 'C:\test\test.xlsm'. In that specific file i would have a named range called 'namedrange'. Now i would like to refer to this namedrange using a function to be created in VBA. I want to use the 'INDEX' function to refer to external, closed workbooks. However, INDEX won't allow me to refer to cells to create that reference. I would need to use INDIRECT: however, INDIRECT won't allow me to use INDEX with closed workbooks. So how would i approach this? I have tried to create function called 'reference' which would link to cell B2, and then use e.g. INDEX(Reference(B2),1,1) to refer to the external workbook' namedrange row 1 column 1. But this doesnt work. Does anyone has any idea? Similarly, I would like to use the function MATCH with these Reference. Much appreciated!

Function Reference(stradd As String) As Range
    Reference = Workbooks(stradd).Range("namedrange")
End Function

David Zemens David Zemens · Accepted Answer · 2013-06-16T13:53:32

You can certainly use the Index/Match functions to reference closed workbooks, e.g.:

Use index/match to find "steve" in column A of a closed workbook named "Test.xlsx" and return the value in corresponding row, column B:

=INDEX('C:\Users\david_zemens\Desktop\[test.xlsx]Sheet1'!$A$1:$B$1,MATCH("steve",'C:\Users\david_zemens\Desktop\[test.xlsx]Sheet1'!$A:$A,FALSE),2)

Obviously, you could change the *lookup_value* of "Steve" to anything, including a cell reference.

Using VBA cell reference to create external reference to closed workbooks

1 Answers