1

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!!

user2204330
  • 13
  • 1
  • 1
  • 4

2 Answers2

3

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
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • I have the other workbook open. But the following formula throws a #REF error: =TRIM(INDIRECT(A1 & "!" & CELL("address",B26))) It gives an error even if I have the whole path in A1. At a closer look, indirect funtion throws this error. Did it work for you? – user2204330 Jan 12 '14 at 11:22
  • I've updated formula in my answer. Try it. If it still doesn't work, show please content of your `A1` cell – Dmitry Pavliv Jan 12 '14 at 11:57
  • This did not work for me. However, thanks for telling me about the indirect function. I created a quick work around. For my purpose, the value of sheet name was changing for each cell and these sheets were in a different work book. I pulled the list of sheet names (200+) using ASAP utilities. Pulled the values I needed using indirect function and finally used a sumproduct function to put my values in the first workbook. – user2204330 Jan 12 '14 at 12:47
  • If you can show me what in your `A1` cell, than I'll try to help you – Dmitry Pavliv Jan 12 '14 at 12:49
  • I have finally completed the task, but here is the content in cell A1 for your record. ''E:\Project\SubFolder\Subfolder\[Workbookname.xlsx]Sheetname' – user2204330 Jan 12 '14 at 13:22
  • 1
    1) you've missed "\" before `[Workbookname.xlsx]`. Correct path should be: `''E:\Project\SubFolder\Subfolder\[Workbookname.xlsx]Sheetname'` 2) for correct path use formula `=TRIM(INDIRECT(A1 & "!" & CELL("address",B26)))` 3) or you can use following awful formula (it will add "\" for you): `=TRIM(INDIRECT(LEFT(A1,FIND("[",A1)-1) & "\" & RIGHT(A1,LEN(A1)-FIND("[",A1)+1) & "!" & CELL("address",B26)))` – Dmitry Pavliv Jan 12 '14 at 13:49
-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)

tjunior
  • 11
  • 2