Excel: Updating dependent/parent cells

Hi,

I have 2 sheets - Data and consultant. Both have a column labelled 'Status' (orange cells) These show the status values of each ID (green cells). The status cells have a data validation dropdown.

Currently the consultant sheet looks up its status value from the data sheet, based on the selected sales consultant.

I would like to edit the spreadsheet so that if the user changes the Status cell drop down value in either the Data or consultant sheet - both sheets will update the appropriate status value for the ID (column C).

Is this possible?

Template-Integrated-Webleadsv9-ee.xlsm
LVL 1
dabug80Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Open 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

Open in new window


and in public Module, add:
Public DoNotUpdate As Boolean

Open in new window

Template-Integrated-Webleadsv9-ee-b.xlsm
dabug80Author Commented:
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?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dabug80Author Commented:
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?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>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.
dabug80Author Commented:
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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
dabug80Author Commented:
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

Open in new window


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

Compile error:

Wrong number of arguments or invalid property assignment
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you may try this instead in Data sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    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)
        For Each ws In Worksheets
            With ws
                If .Name Like "Consultant*" Then
                    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 If
            End With
        Next
    End If
End Sub

Open in new window


but beware that you are tending to make the logic to be more complicated in which you may need better handling for the consultant sheets.
Template-Integrated-Webleadsv9-ee-e.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dabug80Author Commented:
Once again great work. You have weaved some excellent VBA and Excel logic. Hats off to you.

I will sign off on this tomorrow.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
coool, glad that it helps! cheers
dabug80Author Commented:
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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Tks man, I'll take that as a compliment cheers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.