place a number in a cell

[ADiscrete1]

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?

[Max]

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?

[ADiscrete1]

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?

[Max]

"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
---