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:
Many thanks :)
3 Answers
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 SubRight-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.
7I 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.
2Maybe 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.
1
