Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace VLOOKUP results with data | Excel Worksheet Functions | |||
How do I replace my vlookup FORMULA with just the TEXT of results | Excel Worksheet Functions | |||
how do i automatically replace formula with results | Excel Worksheet Functions | |||
Find & Replace - view results | Excel Discussion (Misc queries) | |||
Replace ActiveCell w/Query Results? | Excel Programming |