results of value_if_false = no change to cell

[TomH]

I am doing a payroll program and want to have Y-T-D totals. I use the
following formula and get the results I want on the date that I process the
checks. (I have a seperate cell for each payday of the year):

=IF(TODAY()=DATE(2008,1,25),D13,0)

My problem is, I don't want the value to change back to 0 after the date
passes.
I cannot lock the whole sheet. What I would like to do is have the
value_if_false set to something that will not change the cell. Is this
possible or is there another way to save what is in a cell into another cell
even if the first cell changes it's value.

 

[Teethless mama]

=IF(TODAY()>=DATE(2008,1,25),D13,0)

 

[TomH]

Thanks for the response. However this won't work for me because It changes
the previos week's cells that store the info and I need to keep that info for
my Y-T-D totals.

 

[Ron@Buy]

An unsatisifactory solution to this problem which I have used in the past and
may suit your purposes:
On the Tools menu, click Options, and then click the Calculation tab.
Select the Iteration check box, change the maximum iterations to 1.
Change the "0" in your formula to D13 this results in a circular reference
but the iteration set to 1 should minimise the effect.

 

[TomH]

I am sorry, I guess I am not explaining my situation clearly enough. I did
try this last suggestion but it won't work for what I need.

I have 52 cells (one for each week) that I need to store a "Gross Pay"
amount. Each of these cells gets its value from the same cell (where Gross
pay is calculated). I can get the amount in the cell when I want it there but
with my formula it goes back to 0 when the date is not correct.

I believe that what I need is a different value_if_false that will not
change the current value in the cell if that value is not 0. I just cannot
figure out how to do that.

 

[Ron Rosenfeld]

I believe that what I need is a different value_if_false that will not
change the current value in the cell if that value is not 0. I just cannot
figure out how to do that.

It sounds like you are wanting to do something that cannot be done by a
worksheet function. Functions can only return a value to a cell.

Correct me if I'm wrong:

I assume D13 is the source of the Gross salary for any particular pay date.

You want the cell with the formula filled in with the value in D13 on a certain
date, and then, once it has been filled in, to not change, even if D13 (or the
date) changes.

That is not something that can be done with a worksheet function.

Once the value appears in the cell with the formula, you could do a copy/paste
special/Values to replace the formula with the result as a constant.

You could do something similar with a VBA Macro.

You could change the organization of your data, so that the Gross Salary
calculation is done in a different cell for each paydate.

And there are probably other solutions, too. Hard to recommend one without
knowing more.
--ron