replace a formula with it's results....need help
 

I am not quite followed on how your formulas work but I am guessing the
reason why values change often is J1=Today(). where every day it gets changed
then will change everything else.
why not eliminate Today() function altogether?
Let the user enter the date or create a button that will automatically fill
in today's date. a better approach is in workbook_open event, if the J1 is
empty then put in today's day value. not the formula.


"roverdisc1" wrote:

Here's the problem

-in cell E5- IF(E4<=J1,A1,0) -in cell F5- IF(F4<=J1,A1,0) and so on.

E4, F4 and so on, are fixed dates in two week intervals. J1 is TODAY().
When the calculation is made for E5, A1 will be a certain value. When the
calcualtion is made for F5, the value of A1 will be different. How can I
keep the result of E5 constant when the value of A1 changes?????

My brain is now toast and I can't figure it out. Any help would be
appreciated.

replace a formula with it's results....need help
 

Not exactly what I'm looking for. I'll try to explain differently. If a
cell has an equation IF(E4<=J1,A1,0) I want the formula to run only once. So
that when A1 changes for another equation, the first equation stays.

It's for a simple personal budget. E4 is the date of bank deposit. It is
compared to TODAY() when the spreadsheet is opened and automatically inserts
the deposit amount - A1. Every 2 weeks the same thing will happen. Every
deposit is logged in a different cell and a running total is kept. Problem
is that the deposit amount will change over time. When that happens all the
previous deposits will also change because of the A1 reference.

"Matt - Data Manager - Blue Ridge Telecom" wrote:

I am not quite followed on how your formulas work but I am guessing the
reason why values change often is J1=Today(). where every day it gets changed
then will change everything else.
why not eliminate Today() function altogether?
Let the user enter the date or create a button that will automatically fill
in today's date. a better approach is in workbook_open event, if the J1 is
empty then put in today's day value. not the formula.


"roverdisc1" wrote:

Here's the problem

-in cell E5- IF(E4<=J1,A1,0) -in cell F5- IF(F4<=J1,A1,0) and so on.

E4, F4 and so on, are fixed dates in two week intervals. J1 is TODAY().
When the calculation is made for E5, A1 will be a certain value. When the
calcualtion is made for F5, the value of A1 will be different. How can I
keep the result of E5 constant when the value of A1 changes?????

My brain is now toast and I can't figure it out. Any help would be
appreciated.

replace a formula with it's results....need help
 

Ok, I think I got it.
When I have to create a spreadsheet with the same problem that you have,
Ill use VBA to do the calculation then insert the values. Thisway, you won't
have to worry about things get changed by the formula at the later time.
however, this method requires some kind of user action to let the system know
when to perform the calculation, like a button. Instead of having the users
to enter value into the worksheet directly I'd use a user-form, you can find
that in VB Editor. I am not sure how comfortable you are with programming to
adopt this method.
Another way to fix this is manually replace calculated result with values
only. In other words you are taking out the formula in the cells.
Simply select them, Copy then paste back into the same selection with
PasteSpecials Value only.


Matt
Blue Ridge Telecom

"roverdisc1" wrote:

Not exactly what I'm looking for. I'll try to explain differently. If a
cell has an equation IF(E4<=J1,A1,0) I want the formula to run only once. So
that when A1 changes for another equation, the first equation stays.

It's for a simple personal budget. E4 is the date of bank deposit. It is
compared to TODAY() when the spreadsheet is opened and automatically inserts
the deposit amount - A1. Every 2 weeks the same thing will happen. Every
deposit is logged in a different cell and a running total is kept. Problem
is that the deposit amount will change over time. When that happens all the
previous deposits will also change because of the A1 reference.

"Matt - Data Manager - Blue Ridge Telecom" wrote:

I am not quite followed on how your formulas work but I am guessing the
reason why values change often is J1=Today(). where every day it gets changed
then will change everything else.
why not eliminate Today() function altogether?
Let the user enter the date or create a button that will automatically fill
in today's date. a better approach is in workbook_open event, if the J1 is
empty then put in today's day value. not the formula.


"roverdisc1" wrote:

Here's the problem

-in cell E5- IF(E4<=J1,A1,0) -in cell F5- IF(F4<=J1,A1,0) and so on.

E4, F4 and so on, are fixed dates in two week intervals. J1 is TODAY().
When the calculation is made for E5, A1 will be a certain value. When the
calcualtion is made for F5, the value of A1 will be different. How can I
keep the result of E5 constant when the value of A1 changes?????

My brain is now toast and I can't figure it out. Any help would be
appreciated.