lundi 20 avril 2015

How to fix #VALUE! when UDF references another workbook

I have defined a function in the Module1 of my workbook that looks like this:

Function Header(r As Range) As String
    For i = 1 To r.Row
        If r.Offset(-i, -1).Value = "" Then
            Header = r.Offset(-i).Value
            Exit For
        End If
    Next
End Function

and I call it with a reference to another workbook's cell like this:

=Header('[OtherWorkbook.xlsx]Sheet1'!C34)

and what I get is #VALUE!... until, I open the other workbook, at which point the value magically appears.

how can I force the spreadsheet to fetch the values from the other workbook, even though it isn't open?

Aucun commentaire:

Enregistrer un commentaire