#ref! error when creating input sheet.

[seanhen1984]

I have created an input cell to automatically change which month to start summing an array at from a different data file, in order to calculate month-to-date numbers. However, unless I have the other excel file open, "#REF!" shows up if I enter anything after month 2. Here is the formula I am using:

=SUM(C:\exceldata\[2011 Data.xls]BRANDS (2011)'!$D11:INDEX(C:\exceldata\[2011 Data.xls]BRANDS (2011)'!$D11:$O11,INPUT!$D$3))

Can anyone help?
<!-- / message -->

 

[TiaXL]

have the file open?

or copy the data into a new sheet on the workbook you are doing your calcs on and adjust your formula to look at this range instead ?

Or dont update the links when prompted will stop the formula from re-calculating ??

 

[Comfy]

As far as I'm aware there is no function to pull data from a closed workbook.

 

[VoG]

Maybe Harlan Grove's PULL function http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/