Formula with variable path part

Copper Contributor

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

4 Replies

@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."

@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 

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

@Hans Vogelaar 

 

thank you all.. you are the best