so here is the file
what i added...
on sheetdown sheet
there is list of trucks and types in column S and T
on daily order tab
added truck list in column N (manual...should just run of list in sheetdown)
column P is unique truck list
Column Q is type of truck (vlookup against sheetdown)
^^part of this is redundant but can fix tomorrow if this actually works for you
coding
some changes to your button10_click coding
mainly
With Range("d20").Offset(i + Lastrow, 0).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=OFFSET($P$14,MATCH(" & Chr(34) & t & Chr(34) & ",Q15:Q50,0),0,COUNTIF(Q15:Q50," & Chr(34) & t & Chr(34) & "))"
End With
so insert validation using offset formula with "t" to populate
i also added some code on daily order worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
If Target.Row > 20 And Target.Row < 100 Then
Cells(Range("N14").End(xlDown).Row + 1, 14) = Target.Value
End If
End If
End Sub
can refine the code more so it doesn't do anything spastic but for now it works ok
but essentially it just adds anything between D20:D100 at the bottom of column N
what this does is column P gets updated and duplicates get removed
so if it appears twice in column N it gets taken off column P
play around see if it works for you
Bookmarks