summing a variable range in a closed file...

[mark]

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

 

[hgrove]

mark wrote...

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

etc.
...

Depending on how the source workbook is laid out, it may be possible t
use SUMPRODUCT-based conditional sums. For example, if monthly values t
be summed are in adjacent columns, you could use something like

=SUMPRODUCT(--(COLUMN($J:$U)-9<=VLOOKUP(rgMonth,tblMonth,2,0)),
'H:\...whatever...\[ENG 2004 BUDGET.XLS]120'!$J3:$U3)

If this works given your user's layout, this is the optimal solution
All other alternatives require VBA and will be SLOWER.

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

Not robust. Whenever changes are made in the source workbook, you'l
need to update this 3rd workbook. Unless you plan to do this for you
user every time the source workbook changes, they'd need to do so. Wha
sort of procedure would you advise them to follow to ensure this alway
gets done when it needs to be?

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

Correct. OFFSET requires that its first argument be a range reference
but references into closed files are NEVER range references becaus
Range objects only exist in OPEN workbooks. There's no way around that
As for it updating even though you choose not to update links, OFFSET i
also a volatile function, so its recalculated whenever the worksheet i
which it appears recalculates. That means there's effectively no way t
avoid updating any links that appear inside OFFSET calls (or any othe
volatile function).

If for some reason conditional SUMPRODUCT functions won't suffice AN
VBA is acceptable, your alternatives are listed in the following linke
article. These are your ONLY alternatives.

http://www.google.com/[email protected]

 

[mark]

Not robust. Whenever changes are made in the source
workbook, you'll need to update this 3rd workbook. Unless
you plan to do this for your user every time the source
workbook changes, they'd need to do so. What sort of
procedure would you advise them to follow to ensure this
always gets done when it needs to be?

Hi Harlan,

I agree. The third file is not a good solution. Just
something that I noticed while flipping it around.

Thanks for your other suggestions, and I will attempt the
SUMPRODUCT.

Hopefully, that will work, and I won't need to do the VB
ones, though I could. Just if SUMPRODUCT works, I think
it will be better for them at the moment.

Thanks again.
Mark