I have a column that uses values from predefined set (a drop-down list), implemented using Data Validations. Now if I change some value in the list, the drop down (when open) will immediately offer this new value. However, the table will not auto-update itself, meaning that some of the values in the column will be invalid until I fix them manually.

I have seen solutions based on macros / VBA but isn't there some clever way directly in the Excel UI?

  • Not using data validation. – chuff Jan 10 '13 at 22:58
  • @chuff I am willing to use some other approach if that works, Data Validations are not a requirement. – Borek Bernard Jan 10 '13 at 23:13
  • 1
    Sorry Borek but it isn't possible without a macro or vba. Data validation is about checking the data at entry time. You would need to trigger something to update the cells with that value. – Nick Perkins Jan 10 '13 at 23:39
  • @NickPerkins And isn't there an alternative approach to Data Validations? Some other way to create a drop down? – Borek Bernard Jan 10 '13 at 23:40
  • 1
    @Borek You could use a combobox control, but then that requires VBA as well. – Nick Perkins Jan 10 '13 at 23:48

As i mentioned in the comments, the only way to do this would be with VBA.

Here is one option. I've added comments throughout the code. This assumes that you are using a named range for the validation list named "List" and that it is on the same sheet as the cells being validated.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant

    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

You can also download the example spreadsheet I put together to test this out. (Contains macros!)

  • Could you provide that Dropbox link again? – Digerkam Oct 28 '17 at 12:08

but isn't there some clever way directly in the Excel UI?

I think I know one - at least it seems to meet all your requests:

  1. You need to set dynamically changing Named Region as a source for Data Validation. This may be achieved using OFFSET function. Assuming you have the list of values for drop-down box in Column A of Sheet1 (name matters only for formula), cell A1 has header e.g. List of values, and values are placed starting A2 and below, you should do the following: go Ribbon Formulas > Name Manager, create new Region (let's call it Items) and set its region NOT pointing to the cells, but using the following formula instead: =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1).
  2. Set Data Validation rules: instead of pointing to cells range for List, type =Items - thus your Named region will be used as list items source.
  3. As a result of the above, you'll receive truly dynamic list which meets all your reqs: you may freely change / add items to column A, and these changes will be immediately reflected in dropdown the next time you use it. At the same time, old values will remain intact.

I use this solution for about 2 years in my work. Hope you'll find it useful as well!

P.S. here is the actual sample file: Dynamic Dropdown

  • Oh, I think I misread the input - it seems that you need changed values being updated in the table as soon as you change them. My solution offers dynamic list, but old values will remain unchanged. Sorry for the confusion. – Peter L. Jan 12 '13 at 17:52

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.