Put this in Input sheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G15:I21")) Is Nothing Then Exit Sub
Dim L(21), List, nbr
For Each c In Range("G15:I21").SpecialCells(xlCellTypeConstants, 3)
nbr = nbr + 1: L(nbr) = c.Value
Next
For t = 1 To nbr
For t2 = t To nbr
If L(t2) < L(t) Then x = L(t): L(t) = L(t2): L(t2) = x
Next
Next
For t = 1 To nbr
List = List & L(t) & ","
Next
With Sheets("Sheet2").Range("C2").Validation ' change Sheet2 to yours
output sheet
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub




"Mike Fogleman" skrev:

I have an area for user input on sheet1. This area is 7 rows by 3 columns
(G15:I21). This area is not usually fully populated, but it could be. It
also could be populated vertically or horizontally:
input1 input2 input3
input4
or
input1 input4
input2
input3

I want to use these values in a DV list on other worksheets. My problem is
getting a clean list of these values with no blanks between them. Also, if
possible, having them sorted would be a great finishing touch for the DV
drop-down.
Any suggestions would be greatly appreciated.

Mike F