ignore blanks in dropdown
 

hi,

i have a dropdown box in my sheet. not in a userform. the input range is
like F7:F77 but there are some cells which are blank. how can i get it to
ignore those blank cells so that i do not have a bunch of blank values to
select from the dropdown box. i would NOT Llke to resort column F is at all
possible so that the blank values appear at the bottom. but if this is not
possible, then any other way you would recommend would be apreciated.

thanks in advance,
geebee

ignore blanks in dropdown
 

If your drop-down is generated from Data Validation, then the drop-down will
show blank cells if the list has blank cells. There is a neat way to
"squeeze" out the blanks. Say in B1 thru B12 we have:

dog

cat

mouse


hat
bat
mat

rosebug

In A1 enter 1
In A2 enter:
=IF(B2="","",1+MAX($A$1:A1)) and copy down. We observe:

1 dog

2 cat

3 mouse


4 hat
5 bat
6 mat

7 rosebug

Note that each non-blank has a unique number along side of it.

Finally in C1 thru C7 enter:

=VLOOKUP(ROW(),A$1:B$12,2,0)

The same formula goes in all these C cells We now see:

1 dog dog
cat
2 cat mouse
hat
3 mouse bat
mat
rosebug
4 hat
5 bat
6 mat

7 rosebug

It is column C that we use for the Data Validation.
--
Gary''s Student - gsnu200742


"geebee" wrote:

hi,

i have a dropdown box in my sheet. not in a userform. the input range is
like F7:F77 but there are some cells which are blank. how can i get it to
ignore those blank cells so that i do not have a bunch of blank values to
select from the dropdown box. i would NOT Llke to resort column F is at all
possible so that the blank values appear at the bottom. but if this is not
possible, then any other way you would recommend would be apreciated.

thanks in advance,
geebee

ignore blanks in dropdown
 

That's neat GS. I learned a new trick.

"Gary''s Student" wrote:

If your drop-down is generated from Data Validation, then the drop-down will
show blank cells if the list has blank cells. There is a neat way to
"squeeze" out the blanks. Say in B1 thru B12 we have:

dog

cat

mouse


hat
bat
mat

rosebug

In A1 enter 1
In A2 enter:
=IF(B2="","",1+MAX($A$1:A1)) and copy down. We observe:

1 dog

2 cat

3 mouse


4 hat
5 bat
6 mat

7 rosebug

Note that each non-blank has a unique number along side of it.

Finally in C1 thru C7 enter:

=VLOOKUP(ROW(),A$1:B$12,2,0)

The same formula goes in all these C cells We now see:

1 dog dog
cat
2 cat mouse
hat
3 mouse bat
mat
rosebug
4 hat
5 bat
6 mat

7 rosebug

It is column C that we use for the Data Validation.
--
Gary''s Student - gsnu200742


"geebee" wrote:

hi,

i have a dropdown box in my sheet. not in a userform. the input range is
like F7:F77 but there are some cells which are blank. how can i get it to
ignore those blank cells so that i do not have a bunch of blank values to
select from the dropdown box. i would NOT Llke to resort column F is at all
possible so that the blank values appear at the bottom. but if this is not
possible, then any other way you would recommend would be apreciated.

thanks in advance,
geebee

ignore blanks in dropdown
 

Yes. You can use the same trick to do just about any kind of filter
dynamically.
--
Gary''s Student - gsnu200743


"JLGWhiz" wrote:

That's neat GS. I learned a new trick.

"Gary''s Student" wrote:

If your drop-down is generated from Data Validation, then the drop-down will
show blank cells if the list has blank cells. There is a neat way to
"squeeze" out the blanks. Say in B1 thru B12 we have:

dog

cat

mouse


hat
bat
mat

rosebug

In A1 enter 1
In A2 enter:
=IF(B2="","",1+MAX($A$1:A1)) and copy down. We observe:

1 dog

2 cat

3 mouse


4 hat
5 bat
6 mat

7 rosebug

Note that each non-blank has a unique number along side of it.

Finally in C1 thru C7 enter:

=VLOOKUP(ROW(),A$1:B$12,2,0)

The same formula goes in all these C cells We now see:

1 dog dog
cat
2 cat mouse
hat
3 mouse bat
mat
rosebug
4 hat
5 bat
6 mat

7 rosebug

It is column C that we use for the Data Validation.
--
Gary''s Student - gsnu200742


"geebee" wrote:

hi,

i have a dropdown box in my sheet. not in a userform. the input range is
like F7:F77 but there are some cells which are blank. how can i get it to
ignore those blank cells so that i do not have a bunch of blank values to
select from the dropdown box. i would NOT Llke to resort column F is at all
possible so that the blank values appear at the bottom. but if this is not
possible, then any other way you would recommend would be apreciated.

thanks in advance,
geebee