Excel: Update a value in a named range

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.