Sep 12 2022 07:44 AM
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
Sep 12 2022 08:21 AM
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."
Sep 12 2022 08:23 AM
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.
Sep 12 2022 08:28 AM
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
Sep 12 2022 09:20 AM