Is this designed for Offset Function?

[John]

I wish to populate formulas in cells A2:A20 in Sheet2, the formula's in each
of these cells is simply a reference to Sheet1 A10; A19; A28 etc.

I can't just copy whatever I enter in A2 Sheet2 down as it "skips" 9 rows
each time. Could I use a simple Offset formula for this and secondly how
would I do so? If anyone could explain the logic of it I'd appreciate it

I could of course just manually enter the formulas, but I've got to do the
same for columns B to L

Thanks

 

[ronlim]

If I understand your question correctly, you could use absolut
referencing.

Modify the references within the Cells from A10 to $A$10. Thi
"stickies" the reference and you can copy the formula anywhere and th
references will remain the same.

You could also do $A10 or A$10 so that either the column or ro
references become absolute when you copy.

Hope this helps

 

[John]

Thanks Ronlim

Not quite, my Sheet2 formula cells are continuous, whereas the source on
Sheet1 are not continuous, thus

Sheet1A1 will be referenced to a formula in Sheet2A1
Sheet1A10 will be referenced to a formula in Sheet2A2
Sheet1A19 will be referenced to a formula in Sheet2A3

etc

I just want to copy my Formula in Sheet2A1 down (easily)

 

[David Biddulph]

Thanks Ronlim

Not quite, my Sheet2 formula cells are continuous, whereas the source on
Sheet1 are not continuous, thus

Sheet1A1 will be referenced to a formula in Sheet2A1
Sheet1A10 will be referenced to a formula in Sheet2A2
Sheet1A19 will be referenced to a formula in Sheet2A3

etc

I just want to copy my Formula in Sheet2A1 down (easily)

Yes, OFFSET sounds like a good solution.
=OFFSET(Sheet1!A$1,9*(ROW()-1),0)

 

[John]

Thanks David, thats what I'm after

Yes, OFFSET sounds like a good solution.
=OFFSET(Sheet1!A$1,9*(ROW()-1),0)

 

[Biff]

This is more robust:

=INDEX(Sheet1!A:A,(ROWS($1:1)-1)*9+1)

Doesn't use volatile functions and isn't row dependent. Also, row insertions
won't cause problems!

Biff
..

 

[RagDyeR]

Just to do exactly what the OP requested:

=INDEX(Sheet1!A:A,9*ROWS($1:1)+1)

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

This is more robust:

=INDEX(Sheet1!A:A,(ROWS($1:1)-1)*9+1)

Doesn't use volatile functions and isn't row dependent. Also, row insertions
won't cause problems!

Biff
..

 

[Biff]

Just to do exactly what the OP requested:

Which request?

This one:

each of these cells is simply a reference
to Sheet1 A10; A19; A28 etc.

Or this one:

Sheet1A1 will be referenced to a formula in Sheet2A1
Sheet1A10 will be referenced to a formula in Sheet2A2
Sheet1A19 will be referenced to a formula in Sheet2A3

I went with the 2nd one!

Biff

Just to do exactly what the OP requested:

=INDEX(Sheet1!A:A,9*ROWS($1:1)+1)

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

This is more robust:

=INDEX(Sheet1!A:A,(ROWS($1:1)-1)*9+1)

Doesn't use volatile functions and isn't row dependent. Also, row
insertions
won't cause problems!

Biff
.

 

[RagDyeR]

I never got as far as the second one.

When pressed for time I generally jump from the OP to the response posts of
certain people, you being among them.

So I guess the OP has a choice.<g>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Just to do exactly what the OP requested:

Which request?

This one:

each of these cells is simply a reference
to Sheet1 A10; A19; A28 etc.

Or this one:

Sheet1A1 will be referenced to a formula in Sheet2A1
Sheet1A10 will be referenced to a formula in Sheet2A2
Sheet1A19 will be referenced to a formula in Sheet2A3

I went with the 2nd one!

Biff

Just to do exactly what the OP requested:

=INDEX(Sheet1!A:A,9*ROWS($1:1)+1)

--

Regards,

RD
-------------------------------------------------------------------------- --
-------------------
Please keep all correspondence within the Group, so all may benefit !
-------------------------------------------------------------------------- --
-------------------

This is more robust:

=INDEX(Sheet1!A:A,(ROWS($1:1)-1)*9+1)

Doesn't use volatile functions and isn't row dependent. Also, row
insertions
won't cause problems!

Biff
.