Find previous number and find next number in column
 

In Excel 2007, in column C I have some cells that contain numbers and some
cells that are blank. In column D, for every blank cell in column C I need
to return the cell reference of the previous cell containing a number. For
instance, if C10 is 5, c11 is blank, and c12 is blank, then i need d11 to
return "$C$10" and I need d12 to also return "$C$10". D10 should be blank
since C10 contained a number.

In column E, I need to do the same thing except I need the next non-blank
cell returned rather than the previous.

Find previous number and find next number in column
 

In D2 enter =IF(C2="",IF(D1="",CELL("address",C1),D1),"")
Copy down the column
I'll let you do Part 2 <grin
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"DoubleZ" wrote in message
...
In Excel 2007, in column C I have some cells that contain numbers and some
cells that are blank. In column D, for every blank cell in column C I
need
to return the cell reference of the previous cell containing a number.
For
instance, if C10 is 5, c11 is blank, and c12 is blank, then i need d11 to
return "$C$10" and I need d12 to also return "$C$10". D10 should be blank
since C10 contained a number.

In column E, I need to do the same thing except I need the next non-blank
cell returned rather than the previous.

Find previous number and find next number in column
 

We can do it in two steps:
In D1 enter:
=ADDRESS(ROW(C1),COLUMN(C1))
In D2 enter:
=IF(C2="",D1,ADDRESS(ROW(C2),COLUMN(C2))) and copy down

Here is an example of what we see:

3 $C$1
2 $C$2
$C$2
11 $C$4
$C$4
$C$4
1 $C$7
2 $C$8
3 $C$9
$C$9
4 $C$11
5 $C$12
6 $C$13
$C$13
7 $C$15
$C$15
8 $C$17

Not quite there because the cells in D next to filled cells in C should
appear empty. The next step is to make the D cells adjacent to the filled C
cells appear empty.

Click on D1 and assign Conditional formatting:
FormulaIs:
=B1<"" and pick the font color to match the background color.
Copy the formatting down the column. What we now see is:

3
2
$C$2
11
$C$4
$C$4
1
2
3
$C$9
4
5
6
$C$13
7
$C$15
8


--
Gary''s Student - gsnu200836


"DoubleZ" wrote:

In Excel 2007, in column C I have some cells that contain numbers and some
cells that are blank. In column D, for every blank cell in column C I need
to return the cell reference of the previous cell containing a number. For
instance, if C10 is 5, c11 is blank, and c12 is blank, then i need d11 to
return "$C$10" and I need d12 to also return "$C$10". D10 should be blank
since C10 contained a number.

In column E, I need to do the same thing except I need the next non-blank
cell returned rather than the previous.

Find previous number and find next number in column
 

Are you open to working with helper columns?
If yes then try this
Enter the formulas (including the = sign) below in the cells indicated;
D1=IF(C1="",CELL("address",C1),"")
E1=D1
F1=IF(C1<"",CELL("address",C1),"")
G1=IF(F1="",F1,"")

D2=IF(C2="",CELL("address",C1),"")
E2=IF(AND(C2="",D1=""),D2,"")
F2=IF(C2<"",CELL("address",C2),"")
G2=IF(AND(F2="",G1=""),F3,"")
and copy down...

Col E and F will have what you want...
Note: I have done limited testing so it may not work...

"DoubleZ" wrote:

In Excel 2007, in column C I have some cells that contain numbers and some
cells that are blank. In column D, for every blank cell in column C I need
to return the cell reference of the previous cell containing a number. For
instance, if C10 is 5, c11 is blank, and c12 is blank, then i need d11 to
return "$C$10" and I need d12 to also return "$C$10". D10 should be blank
since C10 contained a number.

In column E, I need to do the same thing except I need the next non-blank
cell returned rather than the previous.

Find previous number and find next number in column
 

Thanks to all of you for your help.

"Sheeloo" wrote:

Are you open to working with helper columns?
If yes then try this
Enter the formulas (including the = sign) below in the cells indicated;
D1=IF(C1="",CELL("address",C1),"")
E1=D1
F1=IF(C1<"",CELL("address",C1),"")
G1=IF(F1="",F1,"")

D2=IF(C2="",CELL("address",C1),"")
E2=IF(AND(C2="",D1=""),D2,"")
F2=IF(C2<"",CELL("address",C2),"")
G2=IF(AND(F2="",G1=""),F3,"")
and copy down...

Col E and F will have what you want...
Note: I have done limited testing so it may not work...

"DoubleZ" wrote:

In Excel 2007, in column C I have some cells that contain numbers and some
cells that are blank. In column D, for every blank cell in column C I need
to return the cell reference of the previous cell containing a number. For
instance, if C10 is 5, c11 is blank, and c12 is blank, then i need d11 to
return "$C$10" and I need d12 to also return "$C$10". D10 should be blank
since C10 contained a number.

In column E, I need to do the same thing except I need the next non-blank
cell returned rather than the previous.