Dynamic reference to external workbooks in formula's

[maartenfct]

Hi All,

I was wondering if there's a method to achieve the following. I work with a large number of externally provided fixed format workbooks. From all those workbooks I need a number of values which are in the same exact cells. See table below for an example of what I would like to achieve. Cell B1 has a reference to one of the external workbooks. But rather than having to manually change the workbook name from in this case test1 to test2, I would like a function which reads the text in column A and inserts the text in the formula so it automatically changes the formula to the correct directory. To have it as efficient as possible, the link to column A should be a drag down function as well if that's possible.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]test1
[/TD]
[TD]='C:\Users\...[test1.xlsx]10.Source-Disposition'!$B$26[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]test2[/TD]
[TD]='C:\Users\...["A2".xlsx]10.Source-Disposition'!$B$26[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]test3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]test4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]test5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hopefully you are able to help!

Cheers,
Maarten

 

[Worf]

Hi

The INDIRECT function could be a solution, but the workbooks would need to be open.
The code below does it:

Sub CreateFormulas()
Const Begin = 100
Dim i%
For i = Begin To Range("a" & Rows.Count).End(xlUp).Row
    Cells(i, 2).Formula = "='c:\pub\[" & Cells(i, 1) & ".xlsm]disposition'!$b$26"
Next
End Sub

 

[maartenfct]

Hi Worf, thank you for your reply. You trew me in the right direction. Solved it following this link.