Mirrored Cells

[phillyfilms]

Is there a way of having two cells mirror each other. In other words if I enter a value in cell A I want it to also appear in cell B. Likewise, if I enter a value in cell B I want it to appear in cell A.

Thanks.

[Yogi Anand]

On 2002-05-09 15:48, phillyfilms wrote:
Is there a way of having two cells mirror each other. In other words if I enter a value in cell A I want it to also appear in cell B. Likewise, if I enter a value in cell B I want it to appear in cell A.

Thanks.

I wonder why you would want to do it! ... anyway, this is what I tried -- in column A,

I put the formula
=OFFSET(A:A,0,1)

and in column B, I put the formula
=OFFSET(B:B,0,-1)

The entries do mirror, however as oon as you enter a value in one of the cells, the formula in that cell is gone. So the mirroring is for one time entry only.

I am sure some Excel GURUs will come up with a more viable option.

[Nimrod]

Here's some code that will do the trick:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

   ' mirror values between colA & colB
   If Target.Column = 1 Then Cells(Target.Row, Target.Column + 1).Value = Target
   If Target.Column = 2 Then Cells(Target.Row, Target.Column - 1).Value = Target

End Sub

OR THIS CODE

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

   ' mirror values between colA & colB
   If Target.Column = 1 Then Cells(Target.Row, 2).Value = Target
   If Target.Column = 2 Then Cells(Target.Row, 1).Value = Target

End Sub

[ This Message was edited by: Nimrod on 2002-05-09 16:42 ]

[ This Message was edited by: Nimrod on 2002-05-09 16:48 ]