Trouble with Formula

[GregR]

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

[cush]

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

[Zack Barresse]

"GregR" <gregrivet@gmail.com> wrote in message
news:1115420201.273028.115660@f14g2000cwb.googlegroups.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
>

[Zack Barresse]

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" <gregrivet@gmail.com> wrote in message
news:1115420201.273028.115660@f14g2000cwb.googlegroups.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
>

[Toppers]

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

[GregR]

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

Greg