Pasting Formula Based on 2 Existing Formulas

[roadkill]

I often use the Excel (2003) feature where you have, for example, the number
2 in cell A1 and the number 4 in Cell A2 and you want A3 to be 6, A4 to be 8,
etc. You just select A1:A2, click in the lower right corner of A2, and drag
down as far as you want. However I've tried to do a similar thing where very
simple formulas are involved and I get unexpected results. For example, if
A1 had the formula "=B1" and A2 had the formula "=B3" I would expect that
using this same method I'd get "=B5" in A3 and "=B7" in A4. But instead I
get "=B3" and "=B5" (followed by ="B5" again then "=B7", "=B7", "=B9", "=B9",
and so on.

Am I doing something wrong or is this feature just not supported where
formulas are concerned?

Thanks,
Will

 

[David Biddulph]

No, that feature is not supported. Use the OFFSET function.
=OFFSET(B$1,2*(ROWS(A$1:A1)-1),) and copy down.

 

[Shane Devenshire]

Hi,

Or the same idea as David:

=OFFSET(B$1,2*(ROW()-1),)

In row 1 cell. or if you want to enter on another row then

=OFFSET(B$1,2*(ROW(A1)-1),)