Problems with a formula

[Mike]

I have a problem with a formula. The formula is below.

=IF(AND('Summary Totals'!$D$46-'Summary
Totals'!$D$51>0,TODAY()=H30),'Summary Totals'!$D$46-'Summary Totals'!$D$51,0)

The problem is this, when it goes in and it matches today's date it does the
various calculation and stores the results in the cell. However, I need to
retain this information as it represents a snapshot of information on that
date. When it next goes into the application the following day the previous
day's information is overwritten with '0' for zero. Obviously this is no
good. If you have any suggestions on how to retain the information by
modifying the formula I would greatly appreciate it.

 

[Dave]

Hi,
One option is to use this short macro, and run it from a button or keyboard
shortcut.
Select the cell you want to stay constant, then run.
The macro just replaces the formula with the result, thereby preventing
recalculation.

Sub LockResults()
Selection.Copy
PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

Regards - Dave.

 

[Pete_UK]

The formula will always calculate the relevant value based on TODAY.
If you want to have a snapshot of the value on a particular day (and
also retain the formula), then you will need to copy it elsewhere.
Select the cell, click <copy>, move cursor to where you want the
snapshot value to be, then Edit | Paste Special | Values (check) | OK
then <Esc>.

Hope this helps.

Pete

 

[mmusterd]

I have a problem with a formula. The formula is below.

=IF(AND('Summary Totals'!$D$46-'Summary
Totals'!$D$510,TODAY()=H30),'Summary Totals'!$D$46-'Summary
Totals'!$D$51,0)

The problem is this, when it goes in and it matches today's date it
does the
various calculation and stores the results in the cell. However, I
need to
retain this information as it represents a snapshot of information on
that
date. When it next goes into the application the following day the
previous
day's information is overwritten with '0' for zero. Obviously this is
no
good. If you have any suggestions on how to retain the information by
modifying the formula I would greatly appreciate it.

Probably the simplest solution is to leave out the 'else' statement in
the if, so
instead of
'Summary Totals'!$D$46-'Summary Totals'!$D$51,0)
it would read
'Summary Totals'!$D$46-'Summary Totals'!$D$51)

Good luck!