Removing Blank Cells

[EBK]

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

 

[EBK]

Thanks to anyone who can help, I forgot to add this before I submitted it.

Sincerely,

Paul

 

[Aladin Akyurek]

I need to create a formula that will remove blank cells from a list sorted in a column.

Paul,

What do you mean by "sorted"? In a sorted column would blanks sink to the bottom of the list.

Aladin

 

[EBK]

This list is for eg:

Coloumn c15:c50

in c15 would be a number or letter arrangement. then in c16 there would be another entry but c17 would be blank. The cells in that range would be filled in randomly and I would like to have all the top cells filled with the data and the bottom cells blank so there is no blank cell in between two cells with data in them. I do not know vba and have only dealt with formulas with excel.

Thanks

Paul

 

[Aladin Akyurek]

On 2002-05-08 01:49, EBK wrote:
This list is for eg:

Coloumn c15:c50

in c15 would be a number or letter arrangement. then in c16 there would be another entry but c17 would be blank. The cells in that range would be filled in randomly and I would like to have all the top cells filled with the data and the bottom cells blank so there is no blank cell in between two cells with data in them. I do not know vba and have only dealt with formulas with excel.

Thanks

Paul

And, you don't want to sort the target range?

Aladin

 

[EBK]

If it is possible yes, but the target range i want sorted is filled with formulas. The formulas in the target range bring information from another sheet to the target range. It only brings what is already in the other sheet and doesn't alter the data. For e.g. sheet 1 has data in random order from a1:a45 sheet two has the target range c5:c49 with the same data from sheet 1 a1:a45 with a "if" formula nested with a vlookup formula that brings the data across if there is something in it for a particular student for eg:

c5 in the target range would have =IF(VLOOKUP($C$14,sheet1$A$7:$AH$421,5,FALSE)=0,"",IF(VLOOKUP($C$14,sheet1$A$7:$AH$421,5,FALSE)="y","3"))

so c6 in the target range would have exactly the same except a 6 where the 5 is in the vlookup formulas, and the 3 would be changed to a 4 at the end of the formula.

where c14 in sheet 2 would have a student number entered, sheet1 a7:ah421 would have the list of student numbers, 5 is the first coloumn that the class list runs along a row in, "3" at the end is the class code and if there is a yes in the box then the class code they are doing is brought over into the target range in vertical coloumn order in sheet 2.

so i would get a vertical list with spaces between the classes a student would be doing and would like to have the classes they are doing at the top of the target range, so if it can be done directly from gathering the info from sheet 1's data that would be great to.

Thanks alot

Paul