Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about "Update Values" Box when using Sum(If)
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about "Update Values" Box when using Sum(If)
=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. Brent E wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF function help | Excel Worksheet Functions | |||
Help with SUMIF function | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF Question Criteria | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |