Formula with variable path part

dbuenolivecom 

INDIRECT is the function you'd normally use, but it doesn't work with references to another workbook unless that is open in Excel, which defeats your purpose. So you'd need VBA.

Let's say you enter the folder name (the variable) in B2, and you want the result in B3.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Me.Range("B2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        On Error Resume Next
        With Me.Range("B3")
            .Formula = "=IFERROR('C:\test\" & Me.Range("B2").Value & "\[gov.xlsb]Results'!D4,"""")"
            If Err Then
                .ClearContents
            Else
                .Value = .Value
            End If
        End With
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

dbuenolivecom 

maybe with the formula...

=INDIRECT("[" & A1 & ".xls]Table1!B2")

A1 contains the file name and B2 contains the data required by the file....

Unfortunately, this only works if the target workbook is also open.

You cannot pass an entire file path to the INDIRECT function.

However, you can prevent errors with the help of the IFERROR function.

Otherwise, I see no way to do this without an open workbook.

If it could be possible then I think with VBA…but I’m not sure.

dbuenolivecom 

Without a more complete description here (and, ideally, a sample workbook), it's hard to offer any more than "Maybe INDIRECT would work for you."