Excel Dynamically Reference cells from other files

Hello here is my question...
I have this formula to access data from another Excel file...
=path\[filename.xls]worksheetname'cellreference
I have many similar files in one directory that have the same worksheetname and data location (cell). I would like to have a drop down box in another cell that would provide a list of filenames to select from in another cell. When the drop down list is changed then the value from that field would update the formula above and its value. Unfortunatly I cannot get this to work and am not sure whether I am doing something wrong or if it can't be done in this context. I would appreciate any help I can get.

Thanks
Marvin

Hi Marvin,

In order to reference a workbook or any objects within a workbook in Excel, the workbook must be open in Excel. For each file you will need to use the Open method to open the file, then access it and read and/or write cells, then Save (if you have changed it) and Close the file.

Damon

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Dynamically Reference cells from other files

Re: Dynamically Reference cells from other files