Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Apr 24 '13 at 10:44
    
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 Apr 24 '13 at 10:54
    
Or possible to combine it with formula ---> =RIGHT(A1, LEN(A1)-1) –  Etan Apr 24 '13 at 10:59
1  
The 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 Apr 24 '13 at 11:14
    
If you can only use formulas, you will need to use helper columns. I've updated my answer above. –  teylyn Apr 24 '13 at 11:20
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.