summing a range in a closed file
 

Hi.

I have a user who would like to be able to sum a range in
a closed file. That in itself is possible, but it's a
fiscal application, and he would like to be able to tell
it how many months to sum, in a YTD manner... 1 for Jan, 2
for Feb, etc.

In order to directly sum the 12 month range (in the closed
file), the formula below works:

=SUM('H:\PROJECTS\OVERHEAD_BUDGETS\Excel_Sheets\[ENG 2004
BUDGET.XLS]120'!$J3:U3)

In order to try to make the number of columns flexible
(j3:j3 for Jan, j3:k3 for Feb, etc.), I did this:

=SUM(OFFSET('[ENG 2004 BUDGET.XLS]120'!$J3,0,0,1,VLOOKUP
(rgMonth,tblMonth,2,0)))

where rgMonth receives the month name, and tblMonth lets
it calc a lookup for the number of columns to sum.

That works, when the external file is open. However, if
the external file is not open, the receiving file stops to
ask if you want to update links (at this time, you can see
that the correct numbers appear on the screen), but when
you tell it not to update the links, it goes to #VALUE.

I just discovered that if I use a third file, which links
to the second file, which links to the original file, this
third file will keep the number when told not to update
links. But that's awfully roundabout.

It appears to be the 'offset' function that is causing the
cells to go to #VALUE, even when told not to update the
links.

Can anyone suggest a formula (it will go into many cells)
that would be able to retain the linked data, without
updating the links?

Thanks.
Mark