I've got some cells in an Excel doc populated from a named range (data/validation/list source=MyNamedRange):


Here, the A1:A3 range is named Foobar

B5:B6 is under data validation with it's source set to 'Foobar'

I'd like to be able to update cell's A2 content, from Bar to Quux, and see automatically B5 cell's content updated to Quux, as it's source has been changed.

It might be performed through a macro, but I don't know how to code this.

Any hints please?

  • Not quite clear to me what you are trying - maybe a screenshot would be helpful? – mindless.panda Mar 26 '10 at 13:38
  • 1
    I've added a screen shot and a workflow – Vinzz Mar 26 '10 at 13:53

This seems dangerous, but I can't see any problem with it. Basically if you change anything in Foobar, it searches every cell on the sheet that has Data Validation. If the DV points to Foobar and the value isn't in the list, then it must have been the value that was changed. It worked with my limited testing. Let me know if you see any flaws.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rCell As Range
    Dim rFound As Range

    'Only run this when a cell in Foobar is changed
    If Not Intersect(Target, Me.Range("Foobar")) Is Nothing Then

        'Go through every data validation cell in the sheet
        For Each rCell In Me.Cells.SpecialCells(xlCellTypeAllValidation).Cells

            'if the DV in the cell points to foobar
            If rCell.Validation.Formula1 = "=Foobar" Then

                'See if the cell's value is in the Foobar list
                Set rFound = Me.Range("Foobar").Find(rCell.Value, , xlValues, xlWhole)

                'If it's not in the list, it must be the one that
                'changed, so changed it
                If rFound Is Nothing Then
                    Application.EnableEvents = False
                        rCell.Value = Target.Value
                    Application.EnableEvents = True
                End If
            End If
        Next rCell
    End If

End Sub

Note that this goes in the Worksheet's module, not a standard module. As always, test code on a copy of your workbook.

  • wow, I managed so far to get the inner part working, and was wondering how to trigger it on each change. Thanks a lot, it saved my day! – Vinzz Mar 26 '10 at 14:45

You would need to use VBA to accomplish this, or have an extra calculation cell for each of B5:B6 that would detect that the cell value is no longer contained within the named range and flag this.

I use to have to maintain a huge workbook that had thousands of such dependencies, it was real nightmare to debug.

This link has more on ways of getting round the one-way nature of the link between validation source range and the target cell.

  • helpful link +1 – datatoo Jun 10 '10 at 14:09

What you are doing now is store the value of A2 in B6. But you need to store a reference to A2. Then, B6 would update automatically. I think "=A2" (without quotation marks) as the field value should do this.

  • 1
    I've confirmed that even if your Data Validation settings are set to a column that uses references to the list instead of the list itself, changing a list member will not update a cell who's current value was set to the list member's old value using a DataValidation dropdown. I think your answer assumed that cell B5 was simply a plain text cell, vs. a cell that had been filled as a result of a data validation drop box. – mindless.panda Mar 26 '10 at 14:19
  • You are right user26453. Anyway, I'm hacking my way through this problem in VBA, with pretty good progress. – Vinzz Mar 26 '10 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.