three-way link between cell values

Hello,
One way you could do this is through the use of VBA. I'm assuming that your workbook only contains the three sheets you mention, and this code is written accordingly.

To use this, go into the VB Editor (Alt+F11), click View, Project Explorer and then double click on the ThisWorkbook icon. Enter this code: -

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ChangedSheet As Worksheet, OtherSheets As Worksheet

'First set a reference to the sheet that had the value changed
Set ChangedSheet = Sh

'Disable application events. If this is not done then this macro
'will run indefinitely.
Application.EnableEvents = False

'Now search through ALL sheets in this workbook. As long as the
'sheet is not the one that the user has changed, change the values
'in the other sheets.
For Each OtherSheets In ThisWorkbook.Worksheets
If OtherSheets.CodeName <> ChangedSheet.CodeName Then
OtherSheets.Range(Target.Address).Value = Target.Value
End If
Next

'Re-enable events.
Application.EnableEvents = True

End Sub

HTH,
Dax.

Couple Questions?

Dax

I know you tried to make this simple for me but I have a few questions and I'm sure I'll be able to make it work.
1) Do I have to define the range Target is looking at
2) Do I have to put something workbook specific into codename.

I guess I am uncertain which variables in the code I am supposed to change. I only changed
OtherSheets.Range("a1").Value = Target.Value

Maybe another way to show me the code is if you could show me what it would look like if it were placed into a brand new workbook (sheet1,2,3) where I wanted to make all cells "a1" equal.

Thanks for your time Dax
Mark

Re: Couple Questions?

Mark,
The code will work exactly as it is. I'll explain briefly - apologies if I'm telling you things you already know.

When certain things happen within Excel such as a user double clicking a worksheet or chart, changing a value of a cell or simply moving the currently selected cell elsewhere then Excel creates an event which can be used from within VBA to change the way in which the application reacts.

In this case, I've used the Workbook_SheetChange event which is fired by Excel every time a user changes a cell in any worksheet in the current workbook. Excel also provides Target, which is a reference to the range changed by the user, and sh, which is a reference to the sheet the user changed. I've simply put this code to react to a user changing a cell i.e. whatever changes a user makes to a cell (or range of cells) the same cells in the other two worksheets will be updated with the same values.

As for the Codename bit, this is included so that the macro doesn't attempt to update the sheet that the user has just changed because that would be pointless.

I hope this is clear, let me know if not.

Regards,
Dax.

Hi Mark

The code Dax wrote will work just fine!

Are you aware you can select a sheet, then holding down your Ctrl key select other sheets ? This will group them, then whatever happens on one sheet will automatically reflect on the other sheets.

Dave
OzGrid Business Applications