Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula trouble | Excel Discussion (Misc queries) | |||
Formula trouble | Excel Discussion (Misc queries) | |||
I'm new at this - having trouble with a formula | Excel Discussion (Misc queries) | |||
Trouble with formula | Excel Discussion (Misc queries) | |||
Trouble with formula | Excel Worksheet Functions |