Copying formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt - 31 Mar 2008 14:28 GMT

I'd like to paste a formula in one column every 5th row referencing cells in
another column in which the rows are spaced together. In other words, I'd
like to paste in cell A1 the formula =B1*3, and in A6 =B2*3, and so on. Is
there a way to do this without entering a separate formula in every 5th row?

Reply to this Message

Pete_UK - 31 Mar 2008 14:39 GMT

Put this in A1:

=INDIRECT("B"&MOD(ROW(A1)+4,5))*3

then copy it to A6, A11, A16 etc.

Hope this helps.

Pete

> I'd like to paste a formula in one column every 5th row referencing cells in
> another column in which the rows are spaced together. In other words, I'd
> like to paste in cell A1 the formula =B1*3, and in A6 =B2*3, and so on. Is
> there a way to do this without entering a separate formula in every 5th row?

Reply to this Message

FiluDlidu - 31 Mar 2008 15:15 GMT

The formula that Pete submitted works, but there is a quick way to avoid
pasting it 200 times if you need it that often.

Select cells A1 to A5 >> copy.
Select an array of cells in column A whose first cell should contain a
formula and whose cell count is DIVIDABLE BY 5, for as far as you want to
have formulas in column A >> paste.

Feelu.

> Put this in A1:
>
[quoted text clipped - 10 lines]
> > like to paste in cell A1 the formula =B1*3, and in A6 =B2*3, and so on.. Is
> > there a way to do this without entering a separate formula in every 5th row?

Reply to this Message