Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Not quite clear to me what you are trying - maybe a screenshot would be helpful? – mindless.panda Mar 26 '10 at 13:38
I've added a screen shot and a workflow – Vinzz Mar 26 '10 at 13:53
up vote 4 down vote accepted

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.

share|improve this answer
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.

share|improve this answer
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.

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .