2 Cells on 2 Sheets - How to reference each other

[sg_in_jax]

Hello!

I have a workbook with 2 sheets.

Currently - Sheet 1, A1 is a dollar amount entered by the user
Sheet 2, B1 references Sheet 1, A1 and is locked.

Now - We would like the user to be able to change Sheet 2, B1 and have
it update Sheet 1, A1 and vice versa. So that the user has the option
of changing the dollar amount in 2 places, rather than on Sheet 1
only.

Is this possible?
Thanks in advance!
Sarah

 

[Dave Peterson]

You can do it by using an event macro that looks for changes. But this can
easily break if the user doesn't allow macros to run--or even turns off events.

I wouldn't use this. I'd allow the user to update a single cell and use a
formula in the other cell to retrieve the value. I think that technique is
safer.

But if you want to try...

This code goes behind the ThisWorkbook Module.

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

If Intersect(Sh.Range("a1"), Target) Is Nothing Then
Exit Sub
End If

If LCase(Sh.Name) = "sheet1" Then
OtherSheetName = "sheet2"
Else
OtherSheetName = "sheet1"
End If

On Error GoTo ErrHandler:

Application.EnableEvents = False
Me.Worksheets(OtherSheetName).Range("a1").Value = Sh.Range("a1").Value

ErrHandler:
Application.EnableEvents = True

End Sub

 

[sg_in_jax]

Thank you Dave for your reply.
I really appreciate it!!!!
Sarah