How do you get rid of blanks in a drop down list?

**nerd999** · 03-23-2008, 12:09 AM

Hi
I have a drop down list which uses =if() to pick students from a separate sheet and this drop down list has got blanks in it because the students are from separate years for example you have year 7 then 8 then 7 then 9 then 8 then 10 then 7.
So my drop down list for all year 7's is something like this
John
blank
bob
blank
blank
smith
Is there a way i can get rid of these blanks ?
Because i need a drop down list that will only show students from one year not so many

In simple words need a formula to get rid blanks tried counta blanck but since i already got a formula there wont work

Help will be much appreciated

**oldchippy** · 03-23-2008, 07:02 AM

Hi,

Try this array formula in B1 to get rid of the blanks, this will consolidate the list, then you could use this column for your data validation.

Please Login or Register  to view this content.

enter the formula with Ctrl+Shift+Enter and auto-fill downwards

**tonymq** · 04-23-2014, 02:51 AM

The above is great if your array starts at A1 but as soon as the array is move to a different row, the formula stops working. What needs to change in the formula if say the array is $A$2:$A$31?

Thanks

**:) Sixthsense :)** · 04-23-2014, 02:56 AM

Originally Posted by tonymq

as soon as the array is move to a different row, the formula stops working

Try this...

=IF(ISNUMBER(SMALL(IF(A$1:A$30<>"",ROW(A$1:A$30)-MIN(ROW(A$1:A$30))+1),ROW())),INDEX(A$1:A$30,SMALL(IF(A$1:A$30<>"",ROW(A$1:A$30)-MIN(ROW(A$1:A$30))+1),ROW(1:1))),"")

**pareshj** · 05-20-2014, 10:25 PM

It is very simple to add or edit drop down list in excel. Check out the below link and follow the steps,

http://snpinfotech.wordpress.com/201...list-in-excel/