Question about "Update Values" Box when using Sum(If)

[Guest]

Good day,

I was able, w/ the help of some of people in this room, to put together a
complex fomula for our CFO last week that allowed him to search for a range
of number in Column A on one workbook, sum their corresponding values in
Column B and place the Sum on a different sheet. This worked beatifully

The formula was based on this basic Sum If formula:
=SUM(IF('Curr Month'!B3:B1001>=2091,IF('Curr Month'!B3:B1001<=2099,'Curr
Month'!C3:C1001)))

This is where the questions come in. My CFO started to build some worksheets
using a combination of this formula. The formulas seemed to be working great
last week and Friday evening; however, for some reason today when he opened
his document, a box popped up showing options to "Update" or "Don't Update".
My CFO can't recall which he pressed, he believes he clicked on "Update".

Now for some reason, anytime he goes into edit a fomula, or try to copy and
paste a formula into an adjacent cell, an "Update Values" Dialogue Box comes
up. (This window looks like a "Save As" window, where he must browse to the
file where the source data is and select the file for the source.) This
quickly becomes very annoying and labor intensive because every single
adaptation he tries to make to a formula brings up this window and is very
time consuming to constantly have to browse and select your file.

Also one of the spreadsheets converted his formulas to the absolute path of
the source data. For instance, just one of the formulas looks like this now:
=(SUM(IF('C:\Financials\Combined Financials\Trial
Balance\[APR06.xls]GLNC3003'!A2:A350>=1400,IF('C:Financials\Combined
Financials\Trial
Balance\[APR06.xls]GLNC3003'!A2:A350<=1499,'C:\Financials\Combined
Financials\Trial Balance\[APR06.xls]GLNC3003'!G2:G350))))*-1


If he browses to the source file when the window pops up, the fomula seems
to work, but if he clicks "Cancel" when the window appears, the cell then
says "#REF".

If he tried to close the file and DIDN'T save changes, when he reopened the
file, the formulas were still converted.

I am curious, did this somehow change a setting in his Excel options? Or why
does this occurr now? And how do we stop this "Update Values" box from
appearing every time he works on an equation or copies and pastes them?

Can we maybe set the file to update the data automatically if necessary?

Any suggestions are greatly appreciated. Thanks.

Cordially,

 

[Dave Peterson]

=sumif() doesn't work with closed workbooks.

So someone rewrote the formula to be:
=sum(if())
(as an array formula)

It looks to me that the workbook that the formula refers to isn't in the
specified folder. Or the workbook is there, but there is no worksheet with that
name in that workbook.

Good day,

I was able, w/ the help of some of people in this room, to put together a
complex fomula for our CFO last week that allowed him to search for a range
of number in Column A on one workbook, sum their corresponding values in
Column B and place the Sum on a different sheet. This worked beatifully

The formula was based on this basic Sum If formula:
=SUM(IF('Curr Month'!B3:B1001>=2091,IF('Curr Month'!B3:B1001<=2099,'Curr
Month'!C3:C1001)))

This is where the questions come in. My CFO started to build some worksheets
using a combination of this formula. The formulas seemed to be working great
last week and Friday evening; however, for some reason today when he opened
his document, a box popped up showing options to "Update" or "Don't Update".
My CFO can't recall which he pressed, he believes he clicked on "Update".

Now for some reason, anytime he goes into edit a fomula, or try to copy and
paste a formula into an adjacent cell, an "Update Values" Dialogue Box comes
up. (This window looks like a "Save As" window, where he must browse to the
file where the source data is and select the file for the source.) This
quickly becomes very annoying and labor intensive because every single
adaptation he tries to make to a formula brings up this window and is very
time consuming to constantly have to browse and select your file.

Also one of the spreadsheets converted his formulas to the absolute path of
the source data. For instance, just one of the formulas looks like this now:
=(SUM(IF('C:\Financials\Combined Financials\Trial
Balance\[APR06.xls]GLNC3003'!A2:A350>=1400,IF('C:Financials\Combined
Financials\Trial
Balance\[APR06.xls]GLNC3003'!A2:A350<=1499,'C:\Financials\Combined
Financials\Trial Balance\[APR06.xls]GLNC3003'!G2:G350))))*-1

If he browses to the source file when the window pops up, the fomula seems
to work, but if he clicks "Cancel" when the window appears, the cell then
says "#REF".

If he tried to close the file and DIDN'T save changes, when he reopened the
file, the formulas were still converted.

I am curious, did this somehow change a setting in his Excel options? Or why
does this occurr now? And how do we stop this "Update Values" box from
appearing every time he works on an equation or copies and pastes them?

Can we maybe set the file to update the data automatically if necessary?

Any suggestions are greatly appreciated. Thanks.

Cordially,