"shail" skrev i en meddelelse
oups.com...
Hi,

I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.

Please help.

Thanks,

Shail


Hi Shail

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 in ordinary use. 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 will contain all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!

NewList will be used as your new validation list.

Enter this formula in the validation source field:

=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))

The validation box will now contain, what you were looking for.

--
Best regards
Leo Heuser

Followup to newsgroup only please.