Forum Discussion

dbuenolivecom's avatar
dbuenolivecom
Copper Contributor
Sep 12, 2022

Formula with variable path part

I would like to create a formula where I could define the variable location: example: =C:\test\variable \[gov.xlsb]Results'!D4 where variable can define the path in another cell for example
excel
Formulas and Functions
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Sep 12, 2022

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

Resources