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

Is this designed for Offset Function?
 

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

Is this designed for Offset Function?
 

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" wrote in
message ...

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

Is this designed for Offset Function?
 

"John" wrote in message
...
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

Is this designed for Offset Function?
 

Thanks David, thats what I'm after

"David Biddulph" wrote in message
...
"John" wrote in message
...
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

Is this designed for Offset Function?
 

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" wrote in message
...
"John" wrote in message
...
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

Is this designed for Offset Function?
 

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" wrote in message
...
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" wrote in message
...
"John" wrote in message
...
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

Is this designed for Offset Function?
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
"John" wrote in message
...
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

Is this designed for Offset Function?
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
"John" wrote in message
...
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