Data Validation drop down list not auto-updating

Question

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.

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

Answer 1

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

Answer 2

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