Moving up blank cells

I am using the formula below to list what students in a reading program got a
particular question wrong on a test.

=IF(N6=1, " ",A6 &" " & B6)

Cell A6 is childs first name and cell B6 is last name of child and NG is the
first question on the test. I want to create a list of children that got the
question wrong. I am working with hundreds of children and the formula works
except the column that contains the list will have sporadic sections of blank
cells because those children got the question right.

How do I move the data up into the next cell or automatically delete the
cells that don't have a childs name in it?

I am using Excel 2003

Any help would be appreciated
Thank you

If you move the names up by any means you will erase the formulas that are
in those cells. Instead do this. Copy the entire column. Select the first
cell of a blank column, any blank column. Do Edit - Paste Special - Values.
That will paste the entire column of names and blank cells without the
formulas. Now sort that one column by itself ascending. Don't include any
other columns in your sort. All the names will move to the top and will be
sorted. HTH Otto
"sbrimley" <[email protected]> wrote in message
news:[email protected]...
>I am using the formula below to list what students in a reading program got
>a
> particular question wrong on a test.
>
> =IF(N6=1, " ",A6 &" " & B6)
>
> Cell A6 is childs first name and cell B6 is last name of child and NG is
> the
> first question on the test. I want to create a list of children that got
> the
> question wrong. I am working with hundreds of children and the formula
> works
> except the column that contains the list will have sporadic sections of
> blank
> cells because those children got the question right.
>
> How do I move the data up into the next cell or automatically delete the
> cells that don't have a childs name in it?
>
> I am using Excel 2003
>
> Any help would be appreciated
> Thank you
>

That would work except, I really need it to be automatic so when the data is
entered the report of children who got the questions wrong is automatically
created. That way the staff that is not familar with excel at all can just
print the report. I found one formula that would work at
www.cpearson.com/excel/noblanks.html

=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)))

Except that since we have a formula in the referencing cell it is not
working.
We are pulling the data from one worksheet and creating a report in another
worksheet, but all within the same workbook, which may be causing another
problem.
Can you think of any solutions?

"Otto Moehrbach" wrote:

> If you move the names up by any means you will erase the formulas that are
> in those cells. Instead do this. Copy the entire column. Select the first
> cell of a blank column, any blank column. Do Edit - Paste Special - Values.
> That will paste the entire column of names and blank cells without the
> formulas. Now sort that one column by itself ascending. Don't include any
> other columns in your sort. All the names will move to the top and will be
> sorted. HTH Otto
> "sbrimley" <[email protected]> wrote in message
> news:[email protected]...
> >I am using the formula below to list what students in a reading program got
> >a
> > particular question wrong on a test.
> >
> > =IF(N6=1, " ",A6 &" " & B6)
> >
> > Cell A6 is childs first name and cell B6 is last name of child and NG is
> > the
> > first question on the test. I want to create a list of children that got
> > the
> > question wrong. I am working with hundreds of children and the formula
> > works
> > except the column that contains the list will have sporadic sections of
> > blank
> > cells because those children got the question right.
> >
> > How do I move the data up into the next cell or automatically delete the
> > cells that don't have a childs name in it?
> >
> > I am using Excel 2003
> >
> > Any help would be appreciated
> > Thank you
> >
>
>
>

Otto,
Thanks for your help, I got it to work. I went back to my orignal formula
and realized that I had a space between my first set of quotes and that was
telling the formula that I got off the Pearson site that the cell wasn't
empty because of the space. Delete the space in the formula and all the
names move up. It is a beautiful thing! Even though it caused me hours of
frustration, I am glad it was something so small
Thanks Again
Suzanne
"Otto Moehrbach" wrote:

> If you move the names up by any means you will erase the formulas that are
> in those cells. Instead do this. Copy the entire column. Select the first
> cell of a blank column, any blank column. Do Edit - Paste Special - Values.
> That will paste the entire column of names and blank cells without the
> formulas. Now sort that one column by itself ascending. Don't include any
> other columns in your sort. All the names will move to the top and will be
> sorted. HTH Otto
> "sbrimley" <[email protected]> wrote in message
> news:[email protected]...
> >I am using the formula below to list what students in a reading program got
> >a
> > particular question wrong on a test.
> >
> > =IF(N6=1, " ",A6 &" " & B6)
> >
> > Cell A6 is childs first name and cell B6 is last name of child and NG is
> > the
> > first question on the test. I want to create a list of children that got
> > the
> > question wrong. I am working with hundreds of children and the formula
> > works
> > except the column that contains the list will have sporadic sections of
> > blank
> > cells because those children got the question right.
> >
> > How do I move the data up into the next cell or automatically delete the
> > cells that don't have a childs name in it?
> >
> > I am using Excel 2003
> >
> > Any help would be appreciated
> > Thank you
> >
>
>
>