Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Median - Ignore blanks | Excel Discussion (Misc queries) | |||
Ignore Blanks | Excel Discussion (Misc queries) | |||
Ignore Blanks in Data Validation | Excel Worksheet Functions | |||
USING IGNORE BLANKS IN FORMULA | Excel Discussion (Misc queries) | |||
Ignore Blanks | Excel Programming |