delete B1 when A1 deleted or cleared

[Bill]

Hi all,

I'm new to excel programming, but I'm sure there has to be an easy
way to do what I need. I'd like to have the contents of a cell (say B1)
when the contents of cell A1 are deleted or cleared. Any thoughts?

Thanks in advance

 

[Bob Greenblatt]

Hi all,

I'm new to excel programming, but I'm sure there has to be an easy
way to do what I need. I'd like to have the contents of a cell (say B1)
when the contents of cell A1 are deleted or cleared. Any thoughts?

Thanks in advance

I'm not sure what you mean in either thread. If you want cell b1 to clear
when a1 ia cleared, try this in b1:
=if(len(a1)=0,"",whatevervalueyouwantewhena1isnotclear)

 

[Bill]

Hi,

I need to be able to enter data into cell (Sheet2!A1) and then have
that data deleted when data in cell (Sheet1!A1) is deleted. Therefore,
cell (Sheet2!A1) can't have formula in it.

Sorry for the confusion

 

[Bob Greenblatt]

Hi,

I need to be able to enter data into cell (Sheet2!A1) and then have
that data deleted when data in cell (Sheet1!A1) is deleted. Therefore,
cell (Sheet2!A1) can't have formula in it.

Sorry for the confusion

Then, the only way to do this is with VBA. You probably want it to operate
on a selection change or worksheet change event. The macro is pretty simple
unless you expect the data to be restored again if the user UNDOES the
delete. Is it just these 2 cells? I have a hunch it isn't, and the problem
is really much more complex than you are describing.

 

[Bill]

Thanks - I don't need and undo, and there will be several cells that
need to be handled in this way. So, when the contents of Sheet1!A1 is
deleted, the contents of Sheet2!A1 is deleted; when Sheet1!A2 is
deleted Sheet2!A2 is deleted and so forth. Its not more complicated
than that.

The reason for this is I'm creating a workbook that contains related
(not in a database sense) information on one sheet (Sheet1) to that on
the other (Sheet2). If the data in Sheet 1 is deleted, I'd like to have
an easy way to remove the corresponding data in Sheet2, without having
to go to Sheet2 to delete it manually.

I suspect VB is the way to do this (with relative references), but I
have no experience with VB at all and was hoping that the code would be
simple enough for a knowledgeable person to type out an example. If
its too complicated, I'll just include a note on worksheet for the user
to remove the data manually.

Thanks again
Bill

 

[Bob Greenblatt]

Thanks - I don't need and undo, and there will be several cells that
need to be handled in this way. So, when the contents of Sheet1!A1 is
deleted, the contents of Sheet2!A1 is deleted; when Sheet1!A2 is
deleted Sheet2!A2 is deleted and so forth. Its not more complicated
than that.

The reason for this is I'm creating a workbook that contains related
(not in a database sense) information on one sheet (Sheet1) to that on
the other (Sheet2). If the data in Sheet 1 is deleted, I'd like to have
an easy way to remove the corresponding data in Sheet2, without having
to go to Sheet2 to delete it manually.

I suspect VB is the way to do this (with relative references), but I
have no experience with VB at all and was hoping that the code would be
simple enough for a knowledgeable person to type out an example. If
its too complicated, I'll just include a note on worksheet for the user
to remove the data manually.

Thanks again
Bill

OK, if it really is that simple, here's a macro that will do it. Just copy
and paste it to the code pane of sheet 1:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Len(Target) = 0 Then
ThisWorkbook.Sheets("Sheet2").Range(Target.Address).ClearContents
End If
End Sub

 

[Bill]

Thanks - that's exactly what I needed.

Bill