Function Values Not Refreshing

[Carlee]

Hi there,

I have a complex function that sums or averages a series a values, based on
month. Occasionally, when i open the workbook and specifically, the sheet,
the function shows as '0' or 'NA' even though there are values that meet the
date criteria. If i go into the function and click in it, then press enter,
the value appears.

Now, i could do this for all 200 functions, on three sheets, but that won't
work. What can i do to make this stop. I have checked Tools > Options >
Calculations and it is set to automatic.

Ideas?

 

[Bernard Liengme]

Are you talking about a function or a formula? If it is user-defined
function, try adding Application.Volatile to the beginning of the code.
best wishes

 

[Carlee]

hi there,

I am not sure the difference between a function and a formula. I think it
is a formula, because, it is not written in VB. It a formula that I entered
into a number of cells. I have included one below. Basically what I am
looking for is a way for the excel workbook to always calculate this for me
rather than me having to constantly go back and enter the formula and press
the 'enter' button to refresh it.

=SUMPRODUCT(--('Daily Reading Master
Log'!A$4:A$403>=DATEVALUE("05/01")),--('Daily Reading Master
Log'!A$4:A$403<=DATEVALUE("5/31")),'Daily Reading Master Log'!AG$4:AG$403)

 

[Bernard Liengme]

That formula should always recalculate when required. Double check that Auto
Calculate is on. Have you tried pressing F9 to force a recalculation?
best wishes