duplicate cells linearly?

[lawpoop]

Hello all -

Is there a way to duplicate a formula in cells were the row reference
grows linearly, instead of simply the number of cells?

For instance, if you have in Sheet2 the formula

Sheet1!$c1

and you duplicate it into rows below, you get

Sheet1!$c2
Sheet1!$c3
Sheet1!$c4
Sheet1!$c5

However, we would like to do :

Sheet1!$c4
Sheet1!$c8
Sheet1!$c12

We have a spreadsheet composed of two worksheets. In the first
worksheet there are cells which are summed every 72 rows. We want
those summed values to appear in worksheet 2, in one row after the
other. Since we have a lot of summed cells, it takes a long time to
type in the proper value! It would save a lot of time if we could
somehow tell Excel to jump a certain number of cells.

I've looked at the OFFSET function, but it seems that we need some way
to tell the constant to multiply the offset by.

For instance, if in worksheet 2 we want:
Sheet1!$c4
Sheet1!$c8
Sheet1!$c12

doing

offset(Sheet1!$c4, 0 4)

won't work, because duplicating it gives us
Sheet1!$c4
offset(Sheet1!$c4, 0 4)
offset(Sheet1!$c5, 0 4)
offset(Sheet1!$c6, 0 4)
offset(Sheet1!$c7, 0 4)

So to use offset, it looks like I need some way to tell Excel to
multiply the row number by the linear factor. E.g., c4 would be 4 *
4.

Any thoughts?

 

[Pete_UK]

If you want to get the equivalent of this in consecutive cells:

Sheet1!$c4
Sheet1!$c8
Sheet1!$c12

then put this in the first cell and copy down:

=INDIRECT("Sheet1!C"&ROW(A1)*4)

In the first cell ROW(A1) equates to 1 so "4" gets tagged on to the
rest of the text. Then in the second cell ROW(A2) equates to 2, to "8"
gets joined on, and so on. You might like to make the multiplier 72,
and you may need to add/subtract a constant depending on where you
want to get your first value from.

Hope this helps.

Pete

 

[Gord Dibben]

=OFFSET(Sheet1!$C$1,4*ROW() -1,0) entered in A1 of Sheet2

Copy down to return values from C4, C8, C12 etc.


Gord Dibben MS Excel MVP