Use file path from a predefined cell in a formula

Question

I think this should be very simple but I have had no luck being able to do it or look for it on the internet. Clearly, I am missing something very obvious.

I have a cell, lets say A1, that contains the following file path:

'C:\[Required file path]Sheetname'

now I want to use this to create a formula in cell B1, lets say,:

=Trim('C:\[Required file path]Sheetname'!B26)

Since this link will be decided dynamically, I want to be able to do something like this:

=Trim(A1!B26)

Where A1 = my required filepath. But this does not work.

Hope I am clear enough in explaining the problem. Thanks in anticipation!!

Answer 1

Try:

=TRIM([A1]Sheet1!B26)

Where A1 holds a reference like: C:\Users\User\Desktop\Test.xlsx.

It may prompt you with a file browser - just select the document which you are trying to reference (e.g. Test.xlsx)

Answer 2

If your second workbook( with path 'C:\[Required file path]Sheetname') is open (but in this case you needn't to use full path to workbook, it's enough to use only WB name), you can use INDIRECT formula (if your A1 contains ''C:\[Required file path]Sheetname'):

=TRIM(INDIRECT(A1 & "!" & CELL("address",B26)))

But if your second workbook is closed, the way I found is to add user defined function to your first workbook and use it:

=TRIM(getValue(A1 & "!" & CELL("address",B26)))

where getValue defines as:

Function getValue(formulaString As String)
   Application.Volatile
   Dim app As Excel.Application
   Dim wb As Workbook

   'set default falue to #REF..if we'd get normal value - we'll change getValue to it'
   getValue = CVErr(xlErrRef)

   'if second WB is open - we can easily evaluate formula and exit function'
   getValue = Evaluate(formulaString)
   If Not IsError(getValue) Then
      Exit Function
   End If

   'if we appear here - second WB is closed...'
   On Error GoTo ErrHandler

   Set app = New Excel.Application
   Set wb = app.Workbooks.Add

   With wb.Sheets(1).Range("A1")
      .Formula = "=" & formulaString
      app.Calculate
      getValue = .Value
   End With

ErrHandler:
   If Not wb Is Nothing Then wb.Close False
   If Not app Is Nothing Then app.Quit
   Set app = Nothing
   Set wb = Nothing
End Function