Trying to create a variable link to another Excel file

[tallman21]

And, I can't seem to get the formula correct. I am trying to give the user the option to choose a month which will then populate a cell with data from a file specific to that month. So in cells A1 they would choose any of the 12 months from a list.

Then, in cell B1, the link would update and show the data. The link is ='C:\2006\January 2006\[January Per Unit Forecast.xls]Per Unit Forecast'!C$3

Obviously, I want to set up the link in such a way that the 2 areas where it shows January instead pull in the value chosen in cell A1 so that it updates with data from the month of my choice.

Thanks

[VBA Noob]

Hi,

Use the indirect function

Here's a simple example

=INDIRECT("'"&A1&" 2006'!C$3")

Looks at a sheet called January 2006 cell C3

VBA Noob

[tallman21]

That seems to work if I am referencing cells within the same workbook. However, for cells outside of the workbook in a different file, it only works if the source file is also open. If it's not open, all cells return #REF!. Updating links also doesn't work in this situation. Heck, maybe it's just not possible.

[VBA Noob]

Hi,

Wasn't sure what you wanted. Indirect does work for closed workbook. This link has a add in you could use

http://www.*****-blog.com/archives/2...sed-workbooks/

VBA Noob