Hi,

I have a Source file that has lots of data and series (File 1). Now I want a separate file (File 2) to just have Charts that fetch data from the source file (File 1). The idea is to open the File 2 and order it to update everything in File 1 and show the updated charts.

I can write a Macro in file 2 to open and update Source file (file 1) it. The problem is how do i refer to the data of file 1 while plotting charts in file 2. My data length in source keeps increasing as more data is added, so I tried using Offset function (in file 2) to refer the source data. This works fine as long as both file 1 and file 2 are open, however the moment file 1 (source file) is closed this fails. I don't want the end user to worry about having opening or seeing the Source file (file 1), he/she should just open the File 2 and see everything.

How do i set dynamic data reference's to data in a different excel file???