Using text in one cell as part of formula to refer to another workbook

Posted by Dean on December 05, 2001 8:27 PM

I want to reference cells in various workbooks depending on what I have in a cell in the current workbook. eg if cell A1 has "Dec 01" then I want to link to cells in the workbook C:\sales\Dec 01.xls
The INDIRECT formula looked to be a good place to start but doesn't seem to go to external workbooks.
I don't know enough about VBA for this to help me.
Thanks

Posted by Colo on December 05, 2001 9:58 PM

Hi! Here's a sample. Please Try.

Sub Test()
ActiveSheet.Hyperlinks.Add _
Anchor:=Range("A1"), _
Address:="C:\sales\" & Range("A1").Value & ".xls"
End Sub