removing blanks from a list

**HPIMICHAEL02** · 12-11-2012, 04:09 AM

good day all,

i have found a formula online which works in their test document but it wont work for me, am i overseeing something?? the first cell returns what i want it to but the rest of the cells return #NUM! error. this normally means theres a math equation it can not compute.

here is my formula :=INDEX(day1_,SMALL(IF(ISBLANK(day1_)+ISERROR(day1_),"",ROW(day1_)-MIN(ROW(day1_))+1),ROW(1:1)))

attached is my sample workbook

list help.xlsx

Thanks in advance

**Fotis1991** · 12-11-2012, 05:30 AM

This is an Array formula and you need to confirm using Control+Shift+Enter. Not just Enter.

But even in this case i am not sure if works for you.

My suggestion is to use just Custom Sorting from your Excel Panel. Why to use a formula and as it is an Array Formula, for this?

**HPIMICHAEL02** · 12-11-2012, 05:42 AM

i am using an array formula because i am making a schedule glance, so every day you just change the date and all people on staff appear on another sheet, i need to sort blanks by formula so i don't lose anyone, when using a custom sort then i lose some people in the future days. i was gonna set up 7 of these lists and have them port to another sheet for every day of the week.

**Fotis1991** · 12-11-2012, 06:18 AM

So, use this one in B2. Also Array.

=INDEX(day1_, SMALL(IF(ISBLANK(day1_),"";ROW(day1_)-MIN(ROW(day1_))+1), ROW(A1)))

BUT: Be carefull. Your A4 cell ISNOT blank. To try if this is TRUE, put this in another empty cell =ISBLANK(A4)