Is this designed for Offset Function?

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

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

"John" <[email protected]> wrote in message
news:[email protected]...
> 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

Thanks David, thats what I'm after

"David Biddulph" <[email protected]> wrote in message
news:[email protected]...
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>

>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

"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> "John" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>
>
>

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


"Biff" <[email protected]> wrote in message
news:%[email protected]...
>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

"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> "John" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>
>
>