Trouble with Formula
 

I have the following formula in C9

=OFFSET(Connie!$O$9,0,(ROW()-9)*2)

which is then copied to next 4 row and formula repeats with an
incremental offset, so that in Row 14 would be

=OFFSET(Connie!$O$9,1,(ROW()-14)*2)

and so on. A Few more examples

R19 would be =OFFSET(Connie!$O$9,3,(ROW()-19)*2)
R24 would be =OFFSET(Connie!$O$9,4,(ROW()-24)*2)
R29 would be =OFFSET(Connie!$O$9,5,(ROW()-29)*2)
R34 would be =OFFSET(Connie!$O$9,6,(ROW()-34)*2)

Notice how every fifth row the row offset increments by 1, and
the column offset by +5.

I can't figure out how to increment these variables accordingly. If
anyone can help with an amended formula or better yet with code, it
would make my life a whole lot easier. I have over 5000 rows to input
these formulae and copy down 4 rows. TIA

Trouble with Formula
 

One approach would be to manually enter the 4 formulas (using the correct
relative/absolute formulas. Then with code copy and paste this RANGE with a
loop:

For i = 1 to 1000
.............
next i


"GregR" wrote:

I have the following formula in C9

=OFFSET(Connie!$O$9,0,(ROW()-9)*2)

which is then copied to next 4 row and formula repeats with an
incremental offset, so that in Row 14 would be

=OFFSET(Connie!$O$9,1,(ROW()-14)*2)

and so on. A Few more examples

R19 would be =OFFSET(Connie!$O$9,3,(ROW()-19)*2)
R24 would be =OFFSET(Connie!$O$9,4,(ROW()-24)*2)
R29 would be =OFFSET(Connie!$O$9,5,(ROW()-29)*2)
R34 would be =OFFSET(Connie!$O$9,6,(ROW()-34)*2)

Notice how every fifth row the row offset increments by 1, and
the column offset by +5.

I can't figure out how to increment these variables accordingly. If
anyone can help with an amended formula or better yet with code, it
would make my life a whole lot easier. I have over 5000 rows to input
these formulae and copy down 4 rows. TIA

Trouble with Formula
 

"GregR" wrote in message
oups.com...
I have the following formula in C9

=OFFSET(Connie!$O$9,0,(ROW()-9)*2)

which is then copied to next 4 row and formula repeats with an
incremental offset, so that in Row 14 would be

=OFFSET(Connie!$O$9,1,(ROW()-14)*2)

and so on. A Few more examples

R19 would be =OFFSET(Connie!$O$9,3,(ROW()-19)*2)
R24 would be =OFFSET(Connie!$O$9,4,(ROW()-24)*2)
R29 would be =OFFSET(Connie!$O$9,5,(ROW()-29)*2)
R34 would be =OFFSET(Connie!$O$9,6,(ROW()-34)*2)

Notice how every fifth row the row offset increments by 1, and
the column offset by +5.

I can't figure out how to increment these variables accordingly. If
anyone can help with an amended formula or better yet with code, it
would make my life a whole lot easier. I have over 5000 rows to input
these formulae and copy down 4 rows. TIA

Trouble with Formula
 

oops.

Maybe you could try using the ..

CELL("row",A1) for a 1, then as you pull down it will increment the row by
one. For a 5 count, multiply it by 5 ...

CELL("row",A1)*5

--
Regards,
Zack Barresse, aka firefytr

"GregR" wrote in message
oups.com...
I have the following formula in C9

=OFFSET(Connie!$O$9,0,(ROW()-9)*2)

which is then copied to next 4 row and formula repeats with an
incremental offset, so that in Row 14 would be

=OFFSET(Connie!$O$9,1,(ROW()-14)*2)

and so on. A Few more examples

R19 would be =OFFSET(Connie!$O$9,3,(ROW()-19)*2)
R24 would be =OFFSET(Connie!$O$9,4,(ROW()-24)*2)
R29 would be =OFFSET(Connie!$O$9,5,(ROW()-29)*2)
R34 would be =OFFSET(Connie!$O$9,6,(ROW()-34)*2)

Notice how every fifth row the row offset increments by 1, and
the column offset by +5.

I can't figure out how to increment these variables accordingly. If
anyone can help with an amended formula or better yet with code, it
would make my life a whole lot easier. I have over 5000 rows to input
these formulae and copy down 4 rows. TIA

Trouble with Formula
 

Try this.

=OFFSET(Connie!$O$9,INT((ROW()-9)/5),(ROW()-(INT((ROW()-9)/5)*5+9))*2)

In your example I think :

R19 would be =OFFSET(Connie!$O$9,3,(ROW()-19)*2)

should be:

R19 would be =OFFSET(Connie!$O$9,2,(ROW()-19)*2)

HTH

"GregR" wrote:

I have the following formula in C9

=OFFSET(Connie!$O$9,0,(ROW()-9)*2)

which is then copied to next 4 row and formula repeats with an
incremental offset, so that in Row 14 would be

=OFFSET(Connie!$O$9,1,(ROW()-14)*2)

and so on. A Few more examples

R19 would be =OFFSET(Connie!$O$9,3,(ROW()-19)*2)
R24 would be =OFFSET(Connie!$O$9,4,(ROW()-24)*2)
R29 would be =OFFSET(Connie!$O$9,5,(ROW()-29)*2)
R34 would be =OFFSET(Connie!$O$9,6,(ROW()-34)*2)

Notice how every fifth row the row offset increments by 1, and
the column offset by +5.

I can't figure out how to increment these variables accordingly. If
anyone can help with an amended formula or better yet with code, it
would make my life a whole lot easier. I have over 5000 rows to input
these formulae and copy down 4 rows. TIA

Trouble with Formula
 

Toppers thank you very much for the formula, just what I needed and you
were right the R19 should have been
=OFFSET(Connie!$O$9,2,(ROW()-1*9)*2).

Greg