Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with a formula
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. -- Mike P |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with a formula
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with a formula
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 On Apr 24, 11:00*am, Mike <mike:discussions.microsoft.com wrote: 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. -- Mike P |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with a formula
Mike;2738793 Wrote: 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. -- Mike P 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! -- mmusterd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problems | Excel Worksheet Functions | |||
Formula Problems | Excel Discussion (Misc queries) | |||
Formula problems | Excel Discussion (Misc queries) | |||
formula problems | Excel Worksheet Functions | |||
Formula Problems | Excel Worksheet Functions |