Is it possible to create linked cells/aliases for cells?

[chamster]

I'd like the following to happen. As i enter a value in cell A1, the same value should appear in cell A2. On the other hand, should i feel like entering a value in cell A2, i wish the newly provided value to appear in cell A1.

By other words, i wish to simulate that A1 and A2 in fact two displays of the same information.

I have a way of solving it but it's butt uggly and involves a bunch of coding. So, i'm looking for a SMOOTH way to solve that. (Of course, the definition of "smooth" may vary a bit but let's disregard it for now, hehe.)

[Cyberdude]

You didn't mention whether you will enter data into the cells more than once. Given that you are using cells A1 and A2, enter the formula =A2 into cell A1, then enter formula =A1 into cell A2. The problem is that you can enter data into these cells only once, because by doing so you will remove the formula from that cell. If you will be entering the data only once, then this could be your solution.

The alternative is to use a VBA macro and event processing to detect when you enter a value into either cell then change the value of the other cell.

I'd be interested in seeing how your solution works.

[Bob Phillips]

I thought I had already posted this

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Address = "$A$1" Then
.Offset(1, 0).Value = .Value
Else
.Offset(-1, 0).Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[chamster]

I must admit i was hoping for a one-liner for a solution. Well, i can sit and hope or i can go and be productive, hehe.

Thanks buddy. At least i know now that there's no easier way around it. Plus, i won't actually have to type the code, only paste in and test. Appreciate it!

And, of course, the value WILL change several times. Good guessing!

[mdmackillop]

A slight variation, if you have more that two interdependent cells
[VBA]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arr As String, Rng As Range, cel As Range
On Error GoTo ws_exit

arr = "A1:A2,A5,A8"

Set Rng = Range(arr)
Application.EnableEvents = False
If Not Intersect(Target, Rng) Is Nothing Then
For Each cel In Rng
cel = Target
Next
End If
ws_exit:
Application.EnableEvents = True
End Sub
[/VBA]

[johnske]

Another option (still not a one-liner though)
[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$1": [A1:A2].FillDown
Case "$A$2": [A1:A2].FillUp
End Select
End Sub
[/vba]or
[VBA]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$1": [A2] = [A1]
Case "$A$2": [A1] = [A2]
End Select
End Sub
[/VBA]