Uniformly link contents of cells

Question

I have a list of systems in a big group that are considered as one. They each have an 'Assessed' status, but I want to somehow link all those systems in the group together, so that if I change the assessment status in any of them, it will update to all those in the group. Is there a way of doing this, apart from assigning one as the 'master' system and having all the others link to that?

Answer 1

If I'm reading you right, what you want is for

  A      B  
------  ------
sys1    Assessed
sys2    Assessed
sys3    Assessed

to become

  A      B  
------  ------
sys1    foo
sys2    foo
sys3    foo

if you type "foo" over any of the "Assessed" cells. The easiest way to do this without using another cell would be to use the worksheet_change() event. Read all about it here.

Here's a little something to get you going. Open the VBA editor (ALT+F11) and double-click on your worksheet name. In the resulting code pane put your worksheet_change() code. Maybe something like:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    Dim startCell As Range
    Set startCell = Range("B1")

    Dim r As Range
    Set r = Range(startCell, startCell.End(xlDown).Address)

    If Not Intersect(Target, r) Is Nothing Then
        r = Target
    End If

End Sub

This will look for changes from B1 to the last contiguous cell below it.