Copying formulas from one cell to the next row down on a worksheet that calculates data from another

Copper Contributor

I want to copy this down a column so that the cells maintain same logic e.g. (Jan!AI$38-jan!aH$38)+(feb!AI$38-AH!$38)+(Mar!AI$38-Mar!AH$38). I want to copy down one cell on another worksheet so that: the formula in the next cell down shows (Jan!AK$38-jan!AJ$38)+(feb!AK$38-AJ!$38)+(Mar!AK$38-Mar!AJ$38) Note: when I copy it only changes the reference columns to the next column to the right...it doesn't know that I want the next set of columns ?  example   B-A  then D-C on so on...

Note that they are not consecutive in nature

Worksheet 1st QTR

=(Jan!AI$38-Jan!AH$38)+(Feb!AI$38-Feb!AH$38)+(Mar!AI$38-Mar!AH$38)
=(Jan!AK$38-Jan!AJ$38)+(Feb!AK$38-Feb!AJ$38)+(Mar!AK$38-Mar!AJ$38)

 

cells from first three worksheets:

S_Lewis_1194_1-1656696716176.png

 

 

 

Windows 10 21h1; excel 

for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20278) 64-bit

1 Reply

@S_Lewis_1194 

Let's say the first formula will be in B2. Use

 

=INDEX(JAN!$38:$38,2*(ROW(B2)-ROW($B$2))+35)-INDEX(JAN!$38:$38,2*(ROW(B2)-ROW($B$2))+34)+INDEX(FEB!$38:$38,2*(ROW(B2)-ROW($B$2))+35)-INDEX(FEB!$38:$38,2*(ROW(B2)-ROW($B$2))+34)+INDEX(MAR!$38:$38,2*(ROW(B2)-ROW($B$2))+35)-INDEX(MAR!$38:$38,2*(ROW(B2)-ROW($B$2))+34)

 

Then fill down.