If cell B1=1 then DELETE cell A1 (excel 2003 formula) - How to do it?

Question

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 :)

Answer 1

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.

Answer 2

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.

Answer 3

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.