Data-Validation Lists

  • Thread starter Thread starter Dan
  • Start date Start date
D

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
 
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
 
Back
Top