Forum Discussion
Enter a data and then delete it quickly
You will need VBA code anyway, since a formula in column H cannot set column I to 0.
I have attached two versions.
The first uses your formula (requiring iterative calculation) in combination with some VBA.
The second doesn't use a recursive formula and hence doesn't need iterative calculation, everything is done in the VBA code.
HansVogelaar First of all let me thank you for your interest.
I saw the example you sent. Interesting, but it doesn't meet my needs. This is certainly due to the lack of circumstantial information in your possession.
Let me get you involved better.
I need H-file to save certain data in memory and add them once every time I add new data (I-file). For that reason I used a recursive formula (*).
Now comes the problem.
In the settings I have entered as the maximum number of iterations equal to 1.
In other words, every time I press enter, thus updating the worksheet, the H-file will increase its value in accordance with I-file.
So I need either the number deleted right after entering it or change the recursive formula to another one that fits better.
Here is a fast-made prospectus.
| A | B | C | D | E | F | G | H | I | |
| 1 | Pool | Qty | Wtg | Qty Rmng | Tot Wtg Rmng | Tot Obt | Obt | ||
2 | Item-A | N1 | M1 | =B2-H2 | =E2*C2 | - (*) | |||
| 3 | Item-B | N2 | M2 | =B3-H3 | =E3*C3 | - | |||
| 4 | Item-C | N3 | M3 | =B4-H4 | =E4*C4 | L3 | |||
| 5 | Item-... | N... | M... | =B...-H... | =E...*C... | ... | |||
| 6 | Total | Nk | Mk | Nk-I | Mk-I | Lk | Xk |
(*) :
=IF(I2=0;H2;H2+I2) "The value must be equal or less than to B-file"
The formula/script/file you sent me, overwrites the h-file and does not allow data to be saved.
You will need VBA code anyway, since a formula in column H cannot set column I to 0.
I have attached two versions.
The first uses your formula (requiring iterative calculation) in combination with some VBA.
The second doesn't use a recursive formula and hence doesn't need iterative calculation, everything is done in the VBA code.