Excel Index Indirect closed workbook

Question

I am aware that you can not use Indirect to reference a closed workbook, but you can use the index function to return a value from a closed workbook such as:

=INDEX([Test.xlsx]Sheet1!C:C,14,1)

returns the value from cell C14

The indirect function also works fine, when the workbook is open; =INDIRECT("'"&$F6&"'!C14")

For the life of me i can not seem to combine the two to get my desired result, that being the value from the closed workbook.

Am I missing something or is this not possible ?

Thanks

Vityata Vityata · Accepted Answer · 2018-06-18T08:57:34

Getting value from a closed workbook with a formula is not possible.

As a workaround, you may create VBA code, which opens the workbook, takes the value and closes it. This is some example code, which opens the file testing.xlsx and writes the value of Worksheets(1).Range("A1") to ThisWorkbook:

Public Sub TestMe()

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\path\testing.xlsx")
    ThisWorkbook.Worksheets(1).Range("A3") = GetSheet1A1(wb)
    wb.Close False

End Sub

Public Function GetSheet1A1(wb As Workbook) As Variant

    GetSheet1A1 = wb.Worksheets(1).Range("A1").Value2

End Function

Excel Index Indirect closed workbook

1 Answers