How to replace a cell's formula with a non-zero calculated value?
 

I have a monthly mileage calculator in which the user enters the month, year,
and mileage. The calculated data can then be printed as that month's
reimbursement sheet. Next month, the same calculator is used with the new
monthly data, etc.

I have a second worksheet to capture each month's data as it is entered, and
then cumulative sum both the mileage and the reimbursement winding up with a
yearly total of those items.

In the second worksheet, I have a formula by each of the 12 months to
inspect the MONTH field in the first worksheet that reads, for example:
IF(Sheet1!$M$6="January", Sheet1!$M$20,), so that when each month's data is
entered, it can be captured in the second worksheet. However, once a month's
data is captured, I want it to be preserved and not subject to being wiped
out when the next month's data is entered in Sheet 1, which is now happening.

How can I set my formulas so that each new monthly entry of data doesn't
wipe out the previously calculated cell data?

How to replace a cell's formula with a non-zero calculated value?
 

Lots of options depending on what you want to do:

- You could Copy-Paste Special: Values at the end of each month.
- Use a macro to set the values once.
- Use a circular reference to set the values once.
- Set up one worksheet per month.
- Add a Month column to your data sheet and don't wipe out the data.
- Do a Save As each month to create a new file for each month.
- Add a new worksheet for each month.

"medecin" wrote:

I have a monthly mileage calculator in which the user enters the month, year,
and mileage. The calculated data can then be printed as that month's
reimbursement sheet. Next month, the same calculator is used with the new
monthly data, etc.

I have a second worksheet to capture each month's data as it is entered, and
then cumulative sum both the mileage and the reimbursement winding up with a
yearly total of those items.

In the second worksheet, I have a formula by each of the 12 months to
inspect the MONTH field in the first worksheet that reads, for example:
IF(Sheet1!$M$6="January", Sheet1!$M$20,), so that when each month's data is
entered, it can be captured in the second worksheet. However, once a month's
data is captured, I want it to be preserved and not subject to being wiped
out when the next month's data is entered in Sheet 1, which is now happening.

How can I set my formulas so that each new monthly entry of data doesn't
wipe out the previously calculated cell data?