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