I would like to create something along the lines of a circular reference
involving text, not numbers.

I have a workbook containing a large number of sheets and at the end is an
index compiling several rows of information from each sheet. What I am hoping
to do is allow changes to certain cells in the index to be reflected in their
sister cells on that item's original sheet, or vice versa, as people are
likely to edit either one without thinking about it not being reflected in
the other position.

Example:

Sheet1, Sheet2, Sheet3, Index

Modification of Sheet1(G9) is mirrored on Index(E15) or vice versa.
Modification of Index(E125) is mirrored on Sheet3(G27) or vice versa.

I imagine this would be done using the SheetChange event to copy the
modified cell contents into the corresponding cell, but am unsure how to
implement this. Any help or pointing in the right direction is great - I
don't mind a having to do a little work to get things going. Thanks!

Use something like this (in sheet1) and similarly for all "linked" sheets:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo errortrap

If Intersect(Target, Range("a1")) Then
Worksheets("Sheet2").Range("a1") = Worksheets("Sheet1").Range("a1").Value
End If


errortrap:
Application.EnableEvents = True

End Sub


"Raymond W." wrote:

I would like to create something along the lines of a circular reference
involving text, not numbers.

I have a workbook containing a large number of sheets and at the end is an
index compiling several rows of information from each sheet. What I am hoping
to do is allow changes to certain cells in the index to be reflected in their
sister cells on that item's original sheet, or vice versa, as people are
likely to edit either one without thinking about it not being reflected in
the other position.

Example:

Sheet1, Sheet2, Sheet3, Index

Modification of Sheet1(G9) is mirrored on Index(E15) or vice versa.
Modification of Index(E125) is mirrored on Sheet3(G27) or vice versa.

I imagine this would be done using the SheetChange event to copy the
modified cell contents into the corresponding cell, but am unsure how to
implement this. Any help or pointing in the right direction is great - I
don't mind a having to do a little work to get things going. Thanks!

Sorry, that should say:

If not Intersect(Target, Range("a1")) is nothing Then


"Sam Wilson" wrote:

Use something like this (in sheet1) and similarly for all "linked" sheets:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo errortrap

If Intersect(Target, Range("a1")) Then
Worksheets("Sheet2").Range("a1") = Worksheets("Sheet1").Range("a1").Value
End If


errortrap:
Application.EnableEvents = True

End Sub


"Raymond W." wrote:

I would like to create something along the lines of a circular reference
involving text, not numbers.

I have a workbook containing a large number of sheets and at the end is an
index compiling several rows of information from each sheet. What I am hoping
to do is allow changes to certain cells in the index to be reflected in their
sister cells on that item's original sheet, or vice versa, as people are
likely to edit either one without thinking about it not being reflected in
the other position.

Example:

Sheet1, Sheet2, Sheet3, Index

Modification of Sheet1(G9) is mirrored on Index(E15) or vice versa.
Modification of Index(E125) is mirrored on Sheet3(G27) or vice versa.

I imagine this would be done using the SheetChange event to copy the
modified cell contents into the corresponding cell, but am unsure how to
implement this. Any help or pointing in the right direction is great - I
don't mind a having to do a little work to get things going. Thanks!

This works perfectly. I was able to easily modify this to watch an entire
range of cells on each page.

Thank you very much for the prompt response!

"Sam Wilson" wrote:

Sorry, that should say:

If not Intersect(Target, Range("a1")) is nothing Then