Assuming source data is in A1 down in Sheet1, with data comprising unique
numbers only with possible blank cells or formula cells returning null
strings in-between, here's one way ..
Put in B1: =IF(A1="","",A1)
Put in C1: =IF(ROW()>COUNT(B:B),"",SMALL(B:B,ROW()))
Select B1:C1, copy down to cover the max extent of data in col A.
Col C will return an auto-ascending sort of the numbers in col A.
Click Insert > Name > Define,
enter under
Names in workbook: MyList
Refers to:
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT(--(Sheet1!$C$1:$C$1000<>"")))
Click OK
Note: Adjust the sumproduct range above to suit the max expected extent in
col C. Use the smallest range large enough to cover.
Now in any sheet of the book, we can create DV lists using MyList as the
source for the data validation. Test it out. In any sheet, select the desired
DV range, then click Data > Validation. Under Allow, choose: List. In the
"Source" box, put: =MyList. Click OK. The DV dropdown will display the
required auto-ascending sort of the source data in Sheet1's col A.