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.
In the first place, this formula causes a circular reference.
In the second place, a formula in one cell (A1) cannot set the value of another cell (N1).
Why not enter the value in A1 directly, bypassing N1 altogether?
But if you really want to do it, you'd need VBA code for this. It would only work if the user allows macros when they open the workbook.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Sample workbook attached.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("N1"), Target) Is Nothing Then
If Range("N1").Value <> 0 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("A1").Value = Range("N1").Value
Range("N1").Value = 0
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End If
End Sub
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.