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?
--
Carlee

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Carlee" wrote in message
...
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?
--
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)
--
Carlee


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Carlee" wrote in message
...
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?
--
Carlee

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Carlee" wrote in message
...
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)
--
Carlee


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Carlee" wrote in message
...
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?
--
Carlee