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.

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.

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.

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.

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.