Need help to create a dynamic data validation list box

[dilfred]

I have a column of unique part numbers created using this code and extracting it from "input" sheet.
This code is then copied down column B on a new sheet called "lists"

{=INDEX(INPUT!$A$6:$A$936, MATCH(0, COUNTIF($B$1:B1, INPUT!$A$6:$A$936), 0))}

I have made this list currently "much longer" than the number of unique items I have (and number of lines currently on "input" sheet, however anticipate that it could potentially be 200+ items by the end of the tolerance analysis.
This is then defined as a list
However once it gets to the bottom of the unique part numbers on the input sheet column B starts to report 0 then n/a n/a n/a etc.

input sheet is something like this
fred1
fred1
fred1
fred1
fred1
fred1
greg1
hedge1
hedge1
hedge1
hedge1
zedge1
zedge1
zedge1

lists sheet is something like this:

fred1
greg1
hedge1
zedge1
0
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
...
EG, these are the 4 unique item numbers in the list on "input" sheet

This is fine, however I am using the list for a data validation list and I don't want the data validation list to include the N/A's or the last 0, I only want the data validation list to give me the "real" part numbers, not the extra invalid ones at the bottom.

Is there any way to make the data validation list adaptive, so that it looks at this list and only selects part numbers?
The way I've previously made them "dynamic" is defining the data list as a table, that way whenever something is added at the bottom, the list updates automatically, however I cannot work out how to make the table expand automatically as another unique item is added on the "input" sheet.

I also tried something like this, but it's not quite there as again it includes the 0, n/a.... etc.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then ' selects and sorts column "B"
'  Columns(Target.Column).Sort _      ' this line and the next 3 sort the column alphabetically, but not necessary as input sheet already sorts them.
'    Key1:=Cells(2, Target.Column), Order1:=xlAscending, _
'    Header:=xlGuess, OrderCustom:=1, _
'    MatchCase:=False, Orientation:=xlTopToBottom
  Target.CurrentRegion.Name = "Lst_Part_Nos"
End If
End Sub

The list of part numbers on "index" sheet is being used in a dependant data validation list like this :- http://www.contextures.com/xlDataVal02.html, but that is not relevant to the question, just giving further context.

Thanks