Data-Validation Lists

[Dan]

Hello,

I am attempting to use a dropdown list from the Data-
Validation menu/toolbar in MS Excel. The cells in which my
dropdown-list is referencing contain formulas. I would
like to remove all the cells that show up as 0 or "" from
my range. The gui has a button for removing blanks,
however it does not remove them if there is a formula in
the cell and shows up blank. Is there a way to do this?

I would also like to combine any duplicates in the list so
that the dropdown menu is not as long and redundant. Is
there a way to combine the duplicates without reformating
the original data that is being referenced?

Thank you

 

[Jason Morin]

One workaround is to pull your list into another column.
Say that your list is in A1:A10. Put this in B1, press
ctrl/shift/enter and fill down until you get consecutive
error messages:

=INDEX($A$1:$A$10,SMALL(IF(($A$1:$A$10<>"")*
($A$1:$A$10<>0),ROW($A$1:$A$10)),ROW()))

Then use this in your Validation > List:

=OFFSET(B1,,,COUNTIF(B:B,"<>#NUM!"))

HTH
Jason
Atlanta, GA