Modify a value in a dependent worksheet and then update back to thesource worksheet.

[Joseph Anthony James]

To All:

Hi all, I have been working on this task in Excel for my company project and I need more guidance. I have one source worksheet that consist of a list items. Within the same workbook, I created three dependent worksheets and each worksheet consists of data linked from the source worksheet. In this case, the same item from the source worksheet may be linked to more than one dependent worksheets. Under normal operation, if I change a value in the source worksheet, excel updates it to the rest of the dependent worksheets.

My question is, how to modify a value in an dependent worksheet, and have that new value reflect back to the source worksheet? (sort of a syncing problem between linked worksheets)

If possible, how to change a value in 1. dependent worksheet number one, and update that value to 2. the source worksheet, and then updates the same value from the source worksheet to 3. dependent worksheet number two, and number three?

 

[Claus Busch]

Hi Joseph,

Am Wed, 25 Dec 2013 20:04:47 -0800 (PST) schrieb Joseph Anthony James:

Hi all, I have been working on this task in Excel for my company project and I need more guidance. I have one source worksheet that consist of a list items. Within the same workbook, I created three dependent worksheets and each worksheet consists of data linked from the source worksheet. In this case, the same item from the source worksheet may be linked to more than one dependent worksheets. Under normal operation, if I change a value in the source worksheet, excel updates it to the rest of the dependent worksheets.

My question is, how to modify a value in an dependent worksheet, and have that new value reflect back to the source worksheet? (sort of a syncing problem between linked worksheets)

If possible, how to change a value in 1. dependent worksheet number one, and update that value to 2. the source worksheet, and then updates the same value from the source worksheet to 3. dependent worksheet number two, and number three?

in a cell can be a formula OR a value but NOT both.
You have to use VBA. Look in the VBA help for Worksheet_Change event


Regards
Claus B.

 

[Joseph Anthony James]

I was trying to avoid to solve this problem in VBA, and thank you Claus for your swift reply in the holiday season. I googled your suggestion on implement Worksheet_Change event to a workbook. Here is the result I get from www.excelforumn.com.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell1 As Range
Dim rCell2 As Range
If Target.Count > 1 Then Exit Sub
Set rCell1 = Range("A1")
Set rCell2 = Range("A2")
Application.EnableEvents = False
Select Case Target.Address
Case rCell1.Address
rCell2.Value = rCell1.Value
Case rCell2.Address
rCell1.Value = rCell2.Value
End Select
Application.EnableEvents = True
End Sub
I tried this code, it worked, both cell A1 and cell A2 are synchronized within the same worksheet. However. My excel VBA problem is more daunting.
1. How to synchronize data between the source worksheet and 3 dependent worksheets?
2. Synchronizing while all of the data in these dependent worksheets were linked from the source worksheet?

 

[Claus Busch]

Hi Joseph,

Am Thu, 26 Dec 2013 15:37:50 -0800 (PST) schrieb Joseph Anthony James:

I was trying to avoid to solve this problem in VBA, and thank you Claus for your swift reply in the holiday season. I googled your suggestion on implement Worksheet_Change event to a workbook. Here is the result I get from www.excelforumn.com.

put following code into the code module "ThisWorkbook":
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Count > 1 Then Exit Sub

Dim wsh As Worksheet
Application.EnableEvents = False

Select Case Target.Address(0, 0)
Case "A1"
For Each wsh In Worksheets
If wsh.Name <> ActiveSheet.Name Then
wsh.Range("A2") = Target
End If
Next
Case "A2"
For Each wsh In Worksheets
If wsh.Name <> ActiveSheet.Name Then
wsh.Range("A1") = Target
End If
Next
End Select
Application.EnableEvents = True
End Sub


Regards
Claus B.