Formula incrementing during copy process

[sliepner]

Apologies in advance if this has previously been answered. I did search through previous queries to see if I could find the solution I require, but could not find the solution

I have a formula which exists over 4 rows on a summary sheet, this is fed from information from other sheets (Sat through Fri). I now need to copy this down the page ( example attached), unfortunately, using either drag or copy paste increases the "Row" numbers (on the example 98,99,100) by 4 instead of 1 i.e. it will jump to 104 on the next copy.

I would be looking to continue the formula down the sheet e.g. next would be required to be
=Sat!G101
=IF(Sat!E101="M",(Sat!V101-J384),(0))
=IF(Sat!E101<>"",(0),(Sat!V101-J384))
=IF(Sat!E101="D",(Sat!V101-J384),(0))

however, when copying it will just jump to =Sat!G104.......etc

Any suggestions would be gratefully received.

[ChemistB]

Checking on the accuracy of your formulas.
In each set as you copy down, you have (in Col A for example) G98 and V98 increasing by 1 to G99 and V99 but you have J376 increasing by 4 to J380. Is this correct?

[ChemistB]

With the active cell A1, Define Names> Offset1 = INT((ROW(A1)-1)/4)

In A1 to A4
=OFFSET(Sat!G98,Offset1,0)
=IF(OFFSET(Sat!E98,Offset1,0)="M",(OFFSET(Sat!V98,Offset1,0)-J376),(0))
=IF(OFFSET(Sat!E98,Offset1,0)<>"",(0),(OFFSET(Sat!V98,Offset1,0)-J376))
=IF(OFFSET(Sat!E98,Offset1,0)="D",(OFFSET(Sat!V98, Offset1,0)-J376),(0))

You can copy and paste them every 4 rows down your page.

[sliepner]

Hi,

Thanks for the info. Yes, the formula is from a summary sheet which is extrapolating information from 8 others, hence the differing accumulations.

I shall away and try the offset function and let you know how I get on.

thanks and regards