Excel, 2 sheets, 2 columns, same value?

Question

I have 2 sheets sheet1 and sheet2 in an excel 2007 file.

In sheet2 I have a column that is managed by a form/macro(with a tree view control). When an element has been selected, the cell is filled with an "x", when it has been unselected, the cell is filled with "" (nothing).

In sheet1 I want to create a column equal to the sheet2 column. So for example: if sheet2!C24 = "x" then sheet1!c24 should also be "x" I also would like it to work both ways. If the user changes sheet1!c24 to "x", then I want sheet2!c24 to take the same value.

Problems: - in Sheet1, I tried sheet1!c24 = sheet2!c24, but then when sheet2!c24 = "", sheet1!c24 displays 0 instead of nothing - in Sheet2, I tried sheet2!c24 = sheet1!c24, but then the cells display the formula (='sheet1!c24') instead of the value...

So basically, what I want is that whatever change you do, in sheet1 or in sheet2, both columns in sheet1 and sheet2 are updated... How can I achieve this?

Answer 1

What I think you need to do is use the Worksheet_Change events for both sheets and if a change is made in the column you are interested in, then you update the same cell in the other sheet.

Something like this would go in the worksheet code module:

Private Sub worksheet_change(ByVal target As Range)
    Dim c As Range

    'Test to see if the cell just changed is
    'in the column we are interested in
    Set c = Application.Intersect(target, Range("A:A"))
    If Not c Is Nothing Then
        'Copy across to other sheet
        If Not beingEdited Then
            beingEdited = True
            Sheet1.Range(target.Address) = target.Value
            beingEdited = False
        End If
    End If
End Sub

You'd need a beingEdited variable to be declared somewhere else with larger scope so that you could avoid the events triggering themselves and Excel getting stuck in a loop.

In the other sheet you'd basically have the same procedure, except that it would reference the first worksheet, e.g. Sheet1.Range(target.Address) = target.Value.

Obviously, you'd have to tweak this to your ranges/sheets.

Answer 2

You've got the right idea, but you probably need to turn off events before making the change, otherwise you'll end up in a loop

Private Sub worksheet_change(ByVal target As Range)
    application.enableevents = false
    sheet1.range("c24").value = sheet2.("c24").value
    application.enableevents = true
end sub

Just make sure you enable events again at the end.

Answer 3

i did something like this where i had a summary sheet and a tests sheet. When I added a new value in tests sheet and it passed (P) a cell in summary sheet will keep increment. This is to keep a count of how many tests passed. here it is:

COUNTIF(tests!$C$5:$C$1017, "P");

hope this helps.