Excel: Updating dependent/parent cells

try use macro instead for columns "Status".

in worksheet "Data", add this in macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If DoNotUpdate = True Then Exit Sub
    If Target.Column = 1 And Target.Row > 1 And Cells(Target.Row, Target.Column) <> "" Then
        ID = Cells(Target.Row, 3)
        With Sheets("Consultant")
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastRow
                If .Cells(i, 3) = ID Then
                    DoNotUpdate = True
                    .Cells(i, 1) = Cells(Target.Row, Target.Column)
                    DoNotUpdate = False
                    Exit Sub
                End If
            Next
        End With
    End If
End Sub

Select allOpen in new window

in worksheet "Consultant", add this in macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If DoNotUpdate = True Then Exit Sub
    If Target.Column = 1 And Target.Row > 9 And Cells(Target.Row, Target.Column) <> "" Then
        ID = Cells(Target.Row, 3)
        With Sheets("Data")
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastRow
                If .Cells(i, 3) = ID Then
                    DoNotUpdate = True
                    .Cells(i, 1) = Cells(Target.Row, Target.Column)
                    DoNotUpdate = False
                    Exit Sub
                End If
            Next
        End With
    End If
End Sub

Select allOpen in new window

and in public Module, add:

Public DoNotUpdate As Boolean

Select allOpen in new window

Template-Integrated-Webleadsv9-ee-b.xlsm

Thanks Ryan.

The problem with the solution is that whenever an orange data validation dropdown on the consultant sheet is changed, the array formula is overwritten.

Is it possible to maintain this functionality, while maintaining the array formula?

>>whenever an orange data validation dropdown on the consultant sheet is changed, the array formula is overwritten.

You can't have a perfect solution as both column A are referring to each other and you need to update both worksheets. In fact, you may no longer need to have formula for the orange color cells

to make your workbook perform better, I have changed a bit the macro scripts.
Template-Integrated-Webleadsv9-ee-c.xlsm

Thanks again Ryan,

Your new example is better, however it still doesn't work 100% (if you change the consultant, the status on the consultant sheet remains the same). Inherently I'm asking Excel to be a database - and it's not a database.

Is it possible to close off this question but award you points for assisting?

>>if you change the consultant, the status on the consultant sheet remains the same

What do you mean by that? As far as I know, you can only change the "Status" in consultant sheet, and the sample I have uploaded was able to handle condition in which when the "Status" in consultant sheet changed, the "Status" in Data sheet will be updated as well, with the condition both sheets' ID are matched.

>>Is it possible to close off this question but award you points for assisting?
yes you can do that, if you think my comments are helpful and you intend to proceed to have another approach for your project.

What do you mean by that?

To see what I mean:

A. On the consultant sheet, change the status of A10.
B. On the consultant sheet, change the consultant (B1)
c. The A10 value will stay the same as changed on step A - but the data sheet status will show a different status for the same ID.

ok, understand your requirement.
In order to fulfill it I have put a temporarily formula back to Consultant's "Status" in case Consultant was changed.

pls have a look on this revised ver and see if it meet your requirement?
Template-Integrated-Webleadsv9-ee-d.xlsm

Wow! That is a great solution. Thanks so much.

I extensively tested it worked well. If I want to duplicate the consultant sheet (to display different consultants at a glance) is it easy to modify line 5 of this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If DoNotUpdate = True Then Exit Sub
    If (Target.Column = 1 Or Target.Column = 3) And Target.Row > 1 And Cells(Target.Row, Target.Column) <> "" Then
        ID = Cells(Target.Row, 3)
        With Sheets("Consultant 1", "Consultant 2", "Consultant 3", "Consultant 4", "Consultant 5")
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastRow
                If .Cells(i, 3) = ID Then
                    DoNotUpdate = True
                    .Cells(i, 1) = Cells(Target.Row, Target.Column)
                    DoNotUpdate = False
                    Exit Sub
                End If
            Next
        End With
    End If
End Sub

Select allOpen in new window

When I make the above modification, I get the VBA error:

Compile error:

Wrong number of arguments or invalid property assignment

Once again great work. You have weaved some excellent VBA and Excel logic. Hats off to you.

I will sign off on this tomorrow.

coool, glad that it helps! cheers

Thank you so much for staying with me and making the tweaks necessary to get the solution to work. One of the best experts exchange experiences I've had. You are a gem.

Tks man, I'll take that as a compliment cheers