Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving up blank cells
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving up blank cells
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)+1ROWS(Blanksrange)-COUNTBLANK(Blanksrange),"",INDIRECT(ADDRESS(SMALL( (IF(Blanksrange<"",ROW(Blanksrange),ROW()+ROWS(Bl anksrange))),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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving up blank cells
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Product Function- Blank Cells | Excel Discussion (Misc queries) | |||
Formula to delete blank cells across multiple columns? | Excel Worksheet Functions | |||
moving cells based certain value | New Users to Excel | |||
Non Blank - Blank Cells???? | Excel Discussion (Misc queries) | |||
Automatic copying data excluding blank cells | Excel Worksheet Functions |