Data Validation drop down list not auto-updating

0
votes

I have a simple column of data on a WorkSheet (PartsList) which filters data from the its respective rows. On another WorkSheet (BoM) within the same WorkBook I am reference this column via a Data Validation 'List'.

The issue that I am having is that when the data within the 'PartsList' WorkSheet is updated the information shown in the drop down list remains the same.

As you can see in the image below, the drop down list hasn't updated the 'part#' although the 'Part Number' and 'Variant' column has. enter image description here enter image description here

And here is the formula that generates the list: enter image description here Is there anyway of making the drop down list auto-update?

2
excelvbavalidation
how is the Data Validation List defined? Without seeing that it's hard to help here. – Scott Holtzman
Did you edit the source range for the data validation? – dwirony
I have added an image of the data validation set up and my formula that generates the list. – Dan M
I suspect you are thinking that a value in drop down list will automatically change by itself. This will not happen even if the values in the list are dynamic. To change the value of a drop-down list, you have to manually go in and choose a new value. The only way to have it update "automatically" is through VBA code manipulation. – Scott Holtzman
@DanM - Looks like it is time to start looking into VBA tutorial. – Scott Holtzman

2 Answers

0
votes

Write an vba code in the background which will update the list whenever the work book is opening. Step 1: Write an vba code which adds data validation filer to the column based on the number of rows in parts list. Step 2: use workbook_open fn to run the macro whenever work book is opened

0
votes

I managed to solve the problem.

I found some VBA code online and made alterations where necessary. See code below with explanations including

 Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Ensure all lists are made from tables and that these tables are named
' in the Name Manager.
' When creating your Data Validation List, instead of selecting a range
' in 'Source', click within 'Source' and press 'F3'. Finally select your
' tables name.
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

On Error GoTo Nevermind
strValidationList = Mid(Target.Validation.Formula1, 2)
strVal = Target.Value
lngNum = Application.WorksheetFunction.Match(strVal, ThisWorkbook.Names(strValidationList).RefersToRange, 0)

' Converts table contents into a formula
If strVal <> "" And lngNum > 0 Then
    Application.EnableEvents = False
    Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"
End If

Nevermind:
    Application.EnableEvents = True

End Sub