Link Cells (Clone? Mirror?) Should be simple (I think)

S

simon chase

Very New to Excel

I want one cell to be a "clone" of another (my choice of word). That is to
say I can change the content of either cell and the other will change.

If I have "John" in cell A1 I want "John" in B1 but I want to be able to
change either and have the other follow. Is this possible? I know I can type
in B1 "=A1" but then I HAVE to modify A1 if I want them both to change rather
than being able to modify either for them to change.

That way I can have 5-6 worksheets that all show the same data but in a
different way but I can use any one of the sheets to modify the whole book
rather than having to use the "master" sheet to make changes.
 
D

Dave Peterson

I think it would be better to either use one "master" worksheet and formulas or
group the 5-6 worksheets (click on the first tab and ctrl-click on subsequent),
then make your change.

Remember to ungroup the worksheets when you're done--any change to one in a
group will be to all!
 
S

simon chase

Thanks for replying Dave but thats not quite what I had in mind.

The Master sheet is exactly what i'm trying to avoid - given that the
different views let you see your data in a different way then wouldn't it be
good to be able to update it in that different way.

As for the grouping; unless i'm misunderstanding (highly possible), this
affects the same exact cell on each sheet which is of limited use.

Any onther ideas anyone?
 
D

Dave Peterson

Sorry, I missed the different addresses portion (which screws up my grouping
suggestions).

You could use a worksheet_change event that updates other sheets based on a
change to one.

And since the code would be pretty much the same in all the worksheets, you
could use the "Workbook_SheetChange" version:


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

Dim mySheetNames As Variant
Dim myAddresses As Variant
Dim res As Variant
Dim iCtr As Long

mySheetNames = Array("sheet1", "sheet2", "sheet3", "sheet4")
myAddresses = Array("a1", "b2", "C3", "d4")

If UBound(mySheetNames) <> UBound(myAddresses) Then
MsgBox "design error"
Exit Sub
End If

res = Application.Match(Sh.Name, mySheetNames, 0)

If IsError(res) Then
'not on a "good" sheet
Exit Sub
End If

On Error GoTo errHandler:

Application.EnableEvents = False
For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
If Me.Worksheets(mySheetNames(iCtr)).Name = Sh.Name Then
'do nothing--that was the sheet that changed
Else
Me.Worksheets(mySheetNames(iCtr)).Range(myAddresses(iCtr)).Value _
= Target.Value
End If
Next iCtr

errHandler:
Application.EnableEvents = True
End Sub

You'll have to make sure you type in the worksheet names correctly and the
addresses, too.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

short cource.
rightclick on the excel icon to the left of File (on the worksheet menu bar).
select View code and paste this into the code window.

Then make your changes to the code and then back to excel to test it out.
 
D

Dave Peterson

I like this one better--it doesn't do as much work.

Option Explicit
Option Base 0
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim mySheetNames As Variant
Dim myAddresses As Variant
Dim res As Variant
Dim iCtr As Long

If Target.Cells.Count > 1 Then Exit Sub

mySheetNames = Array("sheet1", "sheet2", "sheet3", "sheet4")
myAddresses = Array("a1", "b2", "C3", "d4")

If UBound(mySheetNames) <> UBound(myAddresses) Then
MsgBox "design error"
Exit Sub
End If

res = Application.Match(Sh.Name, mySheetNames, 0)

If IsError(res) Then
'not on a "good" sheet
Exit Sub
End If

'added to just check the one cell that's important.
If Intersect(Target, Sh.Range(myAddresses(res - 1))) Is Nothing Then
Exit Sub
end if

On Error GoTo errHandler:

Application.EnableEvents = False
For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
If Me.Worksheets(mySheetNames(iCtr)).Name = Sh.Name Then
'do nothing--that was the sheet that changed
Else
Me.Worksheets(mySheetNames(iCtr)).Range(myAddresses(iCtr)).Value _
= Target.Value
End If
Next iCtr

errHandler:
Application.EnableEvents = True
End Sub

(all the notes still apply, though)
 
Top