Re: 2 Cells on 2 Sheets - How to reference each other by Dave
Dave
Tue Dec 04 11:37:32 PST 2007
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 wrote:
>
> 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