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.

	A	B
1	test1	='C:\Users\...[test1.xlsx]10.Source-Disposition'!$B$26
2	test2	='C:\Users\...["A2".xlsx]10.Source-Disposition'!$B$26
3	test3
4	test4
5	test5

<tbody>
</tbody>

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.