Hi Ricky
I have made a complete description including how to make
the list sorted with formulae only.
Please see below.
"ExcelQuestion" <
[email protected]>
skrev i en meddelelse
Hi Leo,
That was a great technique. Could the dropdown listed in alphabetical
order?
Thanks in advance,
Ricky
Here's one way to do it:
Assumptions:
Name of the range of your current validation list: OldList
1. Make a new list (named NewList), with a number of cells,
which will never be reached under normal conditions.
I have made the list in C3:C24.
2. C2 must be empty or must not contain data found in OldList.
The list cannot start in row 1 with the formula used.
3. In C3 enter this array formula:
=INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0)*(OldList<>""),ROW(OldList)))-MIN(ROW(OldList))+1)
The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in braces {}. Don't enter these
yourself. It's Excel's way of telling, that the formula is an
array formula.
4. Copy C3 down to C4:C24 with the fill handle (the little
square in the lower right corner of the cell).
NewList contains all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!
To get the new list in alphabetical order use this setup:
5. Make another list (named SortList), which has the same size as
NewList.
I have made it in E3:E24.
6. In E3 enter this array formula:
=INDEX(NewList,MATCH(LARGE(MMULT(IF(ISERROR(NewList<=TRANSPOSE(NewList)),0,IF(NewList<=TRANSPOSE(NewList),1,0)),ROW(NewList)^0),ROW()-ROW($E$3)+1),MMULT(IF(ISERROR(NewList<=TRANSPOSE(NewList)),0,IF(NewList<=TRANSPOSE(NewList),1,0)),ROW(NewList)^0),0))
The formula must be entered with <Shift><Ctrl><Enter>
7. Copy E3 down to E4:E24 with the fill handle.
SortList contains NewList sorted.
To use NewList as validation source enter this formula
in the validation source field:
=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))
To use SortList as validation source enter this formula
in the validation source field:
=OFFSET(SortList,,,SUM(NOT(ISERROR(SortList))+0))
Regards
Leo Heuser