I need to create a formula that will remove blank cells from a list sorted in a column. I saw the link to chips excellent site web page and he has a formula there but will not work in my sheet I require it in. I also have this formula that was given to me:
=IF(SMALL(IF(PRINT="",999,(1+ROW(PRINT)-MIN(ROW(PRINT)))),1+ROW(PRINT)-MIN(ROW(PRINT)))=999,"",INDEX(PRINT,SMALL(IF(PRINT="",999,(1+ROW(PRINT)-MIN(ROW(PRINT)))),1+ROW(PRINT)-MIN(ROW(PRINT))))) entered as an array.
whereprint is the name of the cell range that i wish the blank cells to be removed from.
Chips formula he shows is:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) entered as an array.
Chips formula would be easily copied over but i require the coloumn range to be greater in length, chips is approx 15 cells deep and i require mine to be min 35 cells deep in a coloumn(currently the data appears in a coloumn but can be arranged into a row if another formula structure would work better)
I get the first cell in my list blank and the remainding cells all have #NUM! in them when i try entering chips formula as an array. I have made sure that the array range equals the length of the source range and that it is directly beside it as well and out of the way of any surrounding data that may be deleted by rows. I tried variables to entering and cell range names and nothing different happens. Chips formula works fine when I downloaded it so I don't think it would have anything to do with my program or settings of excel. I am also running excel97.
Thanks
Paul
This message was edited by EBK on 2002-05-07 08:31
=IF(SMALL(IF(PRINT="",999,(1+ROW(PRINT)-MIN(ROW(PRINT)))),1+ROW(PRINT)-MIN(ROW(PRINT)))=999,"",INDEX(PRINT,SMALL(IF(PRINT="",999,(1+ROW(PRINT)-MIN(ROW(PRINT)))),1+ROW(PRINT)-MIN(ROW(PRINT))))) entered as an array.
whereprint is the name of the cell range that i wish the blank cells to be removed from.
Chips formula he shows is:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) entered as an array.
Chips formula would be easily copied over but i require the coloumn range to be greater in length, chips is approx 15 cells deep and i require mine to be min 35 cells deep in a coloumn(currently the data appears in a coloumn but can be arranged into a row if another formula structure would work better)
I get the first cell in my list blank and the remainding cells all have #NUM! in them when i try entering chips formula as an array. I have made sure that the array range equals the length of the source range and that it is directly beside it as well and out of the way of any surrounding data that may be deleted by rows. I tried variables to entering and cell range names and nothing different happens. Chips formula works fine when I downloaded it so I don't think it would have anything to do with my program or settings of excel. I am also running excel97.
Thanks
Paul
This message was edited by EBK on 2002-05-07 08:31