Cascading / Offset Formula

[CROY1985]

I need to formula which can be easily replicated in hundreds of cells.

In column A i have a set of percentages. In row 1, starting column B, i have a set of figures.

Cell B2 multiplies B1*A2, Cell C2 multiplies C1*A2 and so on,

Except the next row is off set by one. So C3 multiplies B1*A3

In row 4, Cell D4 multiplies B1*A4. And so on.

Is there an offset formula that i can type once and then just drag it over the entire spreadsheet?

 

[njimack]

=b$1*$a2

 

[CROY1985]

Sorry i havnt been clear enough.

Cell B2 needs to multiply B1 and A2

Cell C2 needs to multiply C1 and A2
Cell C3 needs to multiply B1 and A3

Cell D2 needs to multiply D1 and A2
Cell D3 needs to multiply C1 and A3
Cell D4 needs to multiply B1 and A4

So essentially:

Row number - 2 = Columns to the left
Row number - 1 = Rows to move up

Therefore, as per my examples above:

D4 would look (4-2=2) columns to the left - column B, and (4-1=3) rows above: row 1

And another example:

H7, would look at (7-2=5) columns to the left; column C.

Row number is determined by (7-1=6) rows above; row 1.

Hope this helps now i have laid it out more logically.

 

[njimack]

Try the following in B2 and then copy into the other cells where you require the formula:
=OFFSET(B$1,0,-(ROW()-2))*$A2