MS Excel Data Validation, Ignore Blanks in List

[D Lu]

Hello,

I have a problem utilizing the Data Validation tool in Excel. If I
have a list in one column with the following, for example:

-----------------
Dog
Cat
Rabbit

Frog
Kangaroo
-----------------

See how the list is really six items long? However, if I use a data
validation on a separate cell with a List referring to the above range
of animals, the validation list shows the exact same list:

-----------------
Dog
Cat
Rabbit

Frog
Kangaroo
-----------------

However, how could I fix this without using "Sort" so that the data
validation list in the cell shows the following:

-----------------
Dog
Cat
Rabbit
Frog
Kangaroo
-----------------

Where the rows are only populated by nonblank values. This is
especially handy when my real range is say hundreds of rows long with
many blank cells inbetween the beginning and the end of the range.
I've seen a lot of other forums where people have suggested using the
Offset function; however, that only works to dynamically cut off the
range when there are blanks following the nonblank cells. It does NOT
work for ranges with intermittent blanks.

Thanks,
D

 

[Max]

One way to get there ..

Assume the source for the DV is in Sheet1's A1:A1000
In C1: =IF(A1="","",ROW())
In D1: =INDEX(A:A,SMALL(C:C,ROW()))
Select C11, copy down to D1000

Then you could use this expression as the dynamic range for your DV:
=OFFSET(Sheet1!$D$1,,,SUMPRODUCT(--NOT(ISERROR(Sheet1!$D$1:$D$1000))))
where the height param is given by the sumproduct expression on
the derived col D as the proxy source. The DV droplist will then
return the exact results that you seek, ie expand or contract accordingly.

 

[D Lu]

Max,

I like your idea with the C1 and D1 and it works well. However, if I
have a list of say, six elements like above but one element is blank,
the DV list now shows the five non-blank elements and then a #NUM!
error. If I have seven elements two of which are non-blank and non-
consecutive, then the DV list displays the five non-blank and then two
#NUM! errors. Anyway to remove those #NUM! errors?

Thanks,
D

 

[D Lu]

One way to get there ..

Assume the source for the DV is in Sheet1's A1:A1000
In C1: =IF(A1="","",ROW())
In D1: =INDEX(A:A,SMALL(C:C,ROW()))
Select C11, copy down to D1000

Then you could use this expression as the dynamic range for your DV:
=OFFSET(Sheet1!$D$1,,,SUMPRODUCT(--NOT(ISERROR(Sheet1!$D$1:$D$1000))))
where the height param is given by the sumproduct expression on
the derived col D as the proxy source. The DV droplist will then
return the exact results that you seek, ie expand or contract accordingly.

I just wrote to you... I found and solved the problem. Thanks a
million!!

Regards,
D

 

[Max]

Welcome, glad to hear that.

I just wrote to you... I found and solved the problem. Thanks a
million!!