Data Validation and Blanks in List

[GoneRural]

Hello,

I have been looking on the boards to the resolution for the question I
have but I only keep coming accross the same resolution for something
similiar but not what I am looking for. So hopefully someone can help.

I am trying to create a Data Validation list in Excel but here is the
situation. I have 3 values for instance, value 1 is at A1, value 2 at
A6, value 3 at A11. I want to create the list out of these values but
not have to list them on another sheet without blanks between them.
However, I cannot get the Data Validation list to have just these three
values in it without showing the blanks in between them. I know I
could type in a manual list instead of having a named reference, but
there are actually more than just 3 values, this is just an example. I
have tried just creating a named list with only the three cells
referenced and that doesn't even populate the drop down. So how can I
get these three values to show up in a Data Validation list without the
blanks in the drop down between them??

Thanks in advance for your help,
Ron

 

[Bob Phillips]

Ron,

Best to create a list of non-blanks items and link to that. This formula
builds such a list

=IF(ISERROR(SMALL(IF(A1:A20<>"",ROW(A1:A20)-1,""),ROW(A1:A20)-1)),"",
INDEX(A1:A20,SMALL(IF(A1:A20<>"",ROW(A1:A20)-1,""),ROW(A1:A20)-1)))

as an array formula, applied en-masse to a block of cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)