0
I work with Excel 2003.
If cell B1=1 then DELETE cell A1, and if cell B=0 then UNCHANGED.
How can I do this?
Example: http://oi33.tinypic.com/mwsm03.jpg
Many thanks :)
0
I work with Excel 2003.
If cell B1=1 then DELETE cell A1, and if cell B=0 then UNCHANGED.
How can I do this?
Example: http://oi33.tinypic.com/mwsm03.jpg
Many thanks :)
4
You cannot delete a value in a cell with a formula in another cell. That kind of job requires VBA.
You could have a worksheet change event evaluate column B. If a value in column B is changed by user input, the cell in colum A in the same row can be treated accordingly. For example
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target = 1 Then
Range("A" & Target.Row).Clear
End If
End If
End Sub
Right-click the sheet tab, select "View Code" and paste the above code into the code window.
Another possibility would be to create a helper column that reflects the values of column A depending on the values in another column. Insert a column between A and B and then use something like this in the (now) column B, starting in B1
=if(C1=1,"",A1)
Then you can hide column A if desired.
0
I know I'm late to the party, but another way would be to create a new column (let's say you have A column with your data, B column with your # identifier, and C as your formula column).
column A | Column B | Column C
Banana | 1 | (empty for now)
Phone | 0 | (empty for now)
Cheesecake | 3 | (empty for now)
From here, you'd do C1 with a formula of
=IF(B1=1,"",IF(B1=0,A1,"Value in B not 0 or 1"))
Essentially, if B1 is 1, it'll create a blank cell. If it's not 1, then it'll move on to see if it's 0. If it's 0, then it'll copy the contents of A1. If it's anything else than 0 or 1, you'll get the message that it's not 0 or 1. You can do whatever you like in that "catch". If you want the cell to stay exactly the same if it's not 0 or 1, you could shorten the formula to do something like if it's 1, then "", otherwise same value in A.
1Welcome to Super User. Your approach is already covered in teylyn's answer, although you have a more correct version: consideration of B1=0, and explanation of how it works. Note, though, that this general approach may not work (the question doesn't contain enough information to know for the OP's case): 1. If col A contains formulas rather than hard-coded values, you can only reproduce the result shown in A, not the formula. As the question is written, the cell becomes empty with the desired action. With this solution, the formula can produce a new value if (cont'd) – fixer1234 – 2018-03-16T03:07:29.207
the underlying conditions change. 2. If the OP requires the original cell to be where the action is, you would need to either move columns around (same weakness as #1), or copy, paste-value. Copy/paste would take you back to needing VBA, anyway, and copying a null isn't the same as an empty cell, which could potentially affect other formulas. Suggesting a workaround or alternate approach is fine, but it would be helpful to include any caveats about how the result could be different based on conditions not stated in the question. But kudos for improving on teylyn's suggestion. – fixer1234 – 2018-03-16T03:07:39.680
0
Maybe this is too simple ... In any empty cell type the apostrophe character (') and enter. In all cells in column A open an IF function ... A1 =IF(B1=0,$J$1,existing_Value/Equation). Where $J$1 is the cell with the Apostrophe.
I use this all the time; and it preserves the integrity of the cells in column A.
1So you’re suggesting that the user put the if/then/else logic into Column A? – G-Man Says 'Reinstate Monica' – 2019-11-14T02:28:06.963
solution 2 is awesome – KAs – 2020-02-10T10:35:26.687
There is still a way to somehow do something like this with a formula? (Somehow trick on him) [because in my work I can only use formula] – Etan – 2013-04-24T10:44:06.090
By the way: I found a formula that does something similar, so seems logical to me possible to do what I wanted. ---> Delete A1 cell if B1 is empty: =IF(ISBLANK(B1),"",A1) – Etan – 2013-04-24T10:54:10.657
Or possible to combine it with formula ---> =RIGHT(A1, LEN(A1)-1) – Etan – 2013-04-24T10:59:08.480
1The formula =IF(ISBLANK(B1),"",A1) will not delete the value in cell A1. It will show or not show the value of A1 in the cell where the formula resides. No formula in any cell will change the value in A1. Any formula that changes A1 must be stored in A1. But you cannot have a value AND a formula in A1. Again, you *can not* change a cell value with a formula in another cell. You may want to re-think your data layout. Maybe you can show the desired result in another column. – teylyn – 2013-04-24T11:14:02.347
If you can only use formulas, you will need to use helper columns. I've updated my answer above. – teylyn – 2013-04-24T11:20:34.227
Yes you're right, I understand you. The formula that you wrote down suited me exactly what I need. Thank you for your patience and explanation :) – Etan – 2013-04-25T06:21:16.217