What expression can I use to search previous rows for the nearest
nonblank cell in a specified column (or the current column), then
return a reference to a cell in another column in that row?

Whew, that was a mouthful! See the example below.

I know that I can accomplish what I need by using helper columns. But
I wonder if I can make do without them.

Forgive me if this question has been asked and answered a million
times. I think it has. But I am unable to find the thread at the
moment.

Here is a specific example. Forgive me if the columns (row number and
columns A-D) do not align well.

1: 12/31/2006 101,000 1000 12.00%
2: 1/12/2007 99,000
3: 1/20/2007 110,000
4: 1/31/2007 110,100 (formula) 11.50%
5: 2/3/2007 111,000
6: 2/15/2007 110,000
7: 2/17/2007 110,100
8: 2/28/2007 111,200 (formula) 13.00%

If I were writing the formulas individually, they would be:

C4: =roundup(sumproduct(A2:A4-A1:A3,B1:B3)*D4/365,-2) [= 1100]
C8: =roundup(sumproduct(A5:A8-A4:A7,B4:B7)*D8/365,-2) [= 1200]

I would like a single formula, which I copy into C4, C8 etc. It would
synthesize A2, A1 and B1 in C4, for example, based on the fact that D1
(or C1) is nonblank. (Alternatively, the formula could synthesize the
ranges A2:A4, A1:A3 and B1:B3.) Perhaps the solution would be
structured something like this:

C4: =roundup(sumproduct(offset("search from D3; finds D1",1,-3):A4-
offset("search from D3; finds D1",0,-3):A3,offset("search from D3;
finds D1",0,-2):B3)*D4/365,-2)

C8: =roundup(sumproduct(offset("search from D7; finds D4",1,-3):A8-
offset("search from D7; finds D4",0,-3):A7,offset("search from D7;
finds D4",0,-2):B7)*D8/365,-2)

Despite the obvious inefficiency, I would prefer a solution that
requires only Excel worksheet functions (including ATP functions, if
necessary) in an Excel formula.

(Ah, but what a mess!)

Alternatively, I would consider a VBA solution. I imagine that could
even be much more efficient if the SUMPRODUCT can be done within the
VBA function, since only one search would be required, I assume. But
I don't know how to do that.

Note: The solution can assume that there will be a nonblank cell in
column D (or C) in the first row of the table. Thus, no error-
checking is required.

TIA.