Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
place a number in a cell
I have a column of numbers with blanks in some cells between the numbered
cells. In the next column I have done an if/then statement when there is a blank cell to pull a number above the blank cell but I need a formula to put the number IN the blank cell. Any Suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
place a number in a cell
One play ..
Assume source data is in col A, from row1 down (col A contains data interspersed with "blank" cells) Put in B1: =IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0))) Put in C1: =IF(TRIM(A1)="","",ROW()) Select B1:C1, fill down to the last row of data in col A Col B will extract the data in col A, with all data neatly bunched at the top (w/o intervening blank cells) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ADiscrete1" wrote: I have a column of numbers with blanks in some cells between the numbered cells. In the next column I have done an if/then statement when there is a blank cell to pull a number above the blank cell but I need a formula to put the number IN the blank cell. Any Suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
place a number in a cell
Thank You, Max -
Did not quite get desired effect, but maybe based on what you gave me I can get there. Best - "Max" wrote: One play .. Assume source data is in col A, from row1 down (col A contains data interspersed with "blank" cells) Put in B1: =IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0))) Put in C1: =IF(TRIM(A1)="","",ROW()) Select B1:C1, fill down to the last row of data in col A Col B will extract the data in col A, with all data neatly bunched at the top (w/o intervening blank cells) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ADiscrete1" wrote: I have a column of numbers with blanks in some cells between the numbered cells. In the next column I have done an if/then statement when there is a blank cell to pull a number above the blank cell but I need a formula to put the number IN the blank cell. Any Suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
place a number in a cell
"ADiscrete1" wrote:
Thank You, Max - Did not quite get desired effect, but maybe based on what you gave me I can get there. Sorry about that. On 2nd reading, think Debra's page (below) is a closer fit to your orig post: http://www.contextures.com/xlDataEntry02.html Excel -- Data Entry -- Fill Blank Cells Fill Blank Cells Fill Blank Cells Programmatically -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Insert row number into a cell? | Excel Worksheet Functions | |||
read a column of names and place a number in the next cell | Excel Discussion (Misc queries) | |||
Defining a number in a cell by text then subtracting it by the tex | Excel Worksheet Functions |