Forum Discussion
Enter a data and then delete it quickly
Hello everybody.
There is a solution that allows me to enter a data and then delete it quickly?
In other words, once I have entered the data and pressed enter, for some reason, I have to go back to zeroing it.
There is something like a shortcut or something else that can delete the data entered efficiently?
I already tried something like this, but it only return FALSE in the cell where it's placed and it doesn't do anything in particular.
=IF(N1=0,A1,N1=0) "suppose the formula is placed in cell A1 and N1 is the cell that have to be zeroed"
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.
3 Replies
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 SubHansVogelaar 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.
