Forum Discussion
Copying formulas from one cell to the next row down on a worksheet that calculates data from another
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:
Windows 10 21h1; excel
for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20278) 64-bit
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.
