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 absolute referencing.

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

You could also do $A10 or A$10 so that either the column or row 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)



"ronlim" <ronlim.29wo6o_1151151004.7597@excelforum-nospam.com> wrote in
message news:ronlim.29wo6o_1151151004.7597@excelforum-nospam.com...
>
> If I understand your question correctly, you could use absolute
> referencing.
>
> Modify the references within the Cells from A10 to $A$10. This
> "stickies" the reference and you can copy the formula anywhere and the
> references will remain the same.
>
> You could also do $A10 or A$10 so that either the column or row
> references become absolute when you copy.
>
> Hope this helps.
>
>
> --
> ronlim
> ------------------------------------------------------------------------
> ronlim's Profile:
> http://www.excelforum.com/member.php...o&userid=35738
> View this thread: http://www.excelforum.com/showthread...hreadid=555216
>

[David Biddulph]

"John" <r.@9999yahoo.co.uk> wrote in message
news:e7jah8$5j7$1@reader01.news.esat.net...
> 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)
--
David Biddulph

[John]

Thanks David, thats what I'm after

"David Biddulph" <david@biddulph.org.uk> wrote in message
news:ibWdnUudMpm8rADZRVnyvA@bt.com...
> "John" <r.@9999yahoo.co.uk> wrote in message
> news:e7jah8$5j7$1@reader01.news.esat.net...
>> 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)
> --
> David Biddulph
>

[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
..
"David Biddulph" <david@biddulph.org.uk> wrote in message
news:ibWdnUudMpm8rADZRVnyvA@bt.com...
> "John" <r.@9999yahoo.co.uk> wrote in message
> news:e7jah8$5j7$1@reader01.news.esat.net...
>> 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)
> --
> David Biddulph
>

[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 !
----------------------------------------------------------------------------
-------------------

"Biff" <biffinpitt@comcast.net> wrote in message
news:O0hcI07lGHA.3340@TK2MSFTNGP05.phx.gbl...
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
..
"David Biddulph" <david@biddulph.org.uk> wrote in message
news:ibWdnUudMpm8rADZRVnyvA@bt.com...
> "John" <r.@9999yahoo.co.uk> wrote in message
> news:e7jah8$5j7$1@reader01.news.esat.net...
>> 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)
> --
> David Biddulph
>