Formula autofill skips reference cells

[AWKodiak]

Basically, I want autofill to skip reference columns at a static rate.
I have the formula "=SUM('FPY Data'!K5:K54)-SUM('FPY Data'!L5:L54)" in column E. When I drag autofill across column F is "=SUM('FPY Data'!L5:L54)-SUM('FPY Data'!M5:M54)" but I want it to be "=SUM('FPY Data'!R5:R54)-SUM('FPY Data'!S5:S54)"
I tried filling in a few rows manually and then selecting them all to autofill hoping that it would pick up the pattern, but that did not work. I also messed around with the Offset function, but it would have to increase the offset by 6 columns each time.
If it's easier to just make a macro in VBA to do this, I would give it a try.

 

[Peter_SSs]

Welcome to the MrExcel board!

You might need to adjust the 'FPY Data' range columns (I have used column ZZ as the last data column) as we have no idea how big it is but try this in column E and drag across.

=LET(fc,COLUMNS($E:E)*7-6,SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc))-SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc+1)))

 

[AWKodiak]

Welcome to the MrExcel board!

You might need to adjust the 'FPY Data' range columns (I have used column ZZ as the last data column) as we have no idea how big it is but try this in column E and drag across.

=LET(fc,COLUMNS($E:E)*7-6,SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc))-SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc+1)))

Thanks for the help. It works, however, I don't think this will scale. The last column is unknown. I plan to archive this quarterly so it will need to go out 500+ columns. perhaps if I knew the logic behind the formula, then I could support it and when something inevitably breaks in the future, I can fix it. I am also looking into pivot tables as a solution.

 

[Peter_SSs]

The last column is unknown. I plan to archive this quarterly so it will need to go out 500+ columns.

I presume that you are referring to sheet 'FPY Data'. The formula (using $ZZ column as above) already looks out to about 700 columns. If you don't think that is enough you could just change $ZZ to, say, $AZZ which covers around 1,300 columns or anything else you choose to be sure to be big enough. $XFD if you want to be certain and cover right out to column 16,384.

If we were to investigate other ways, it would be useful to see what is actually on the 'FPY Data' sheet, particularly in relation to any headers on that sheet.

if I knew the logic behind the formula

fc is just a 'name' that holds a value. With the first formula in column E ..
COLUMNS($E:E)*7-6 = 1*7-6 = 1
Therefore SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc)) = SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,1)) which sums column 1 of the range. That is, column K
Similarly SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc+1)) = SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,1+1)) = SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,2)) which sums column 2 of the range. That is, column L
The subtraction then gives the same result as your original formula in column E

When the formula is dragged to column F, it becomes
=LET(fc,COLUMNS($E:F)*7-6,SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc))-SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc+1)))
So fc gets the value COLUMNS($E:F)*7-6 = 2*7-6 = 8
Therefore SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc)) = SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,8)) which sums column 8 of the range. That is, column R
Similarly SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,fc+1)) = SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,8+1)) = SUM(INDEX('FPY Data'!$K$5:$ZZ$54,0,9)) which sums column 9 of the range. That is, column S
Subtraction then gives column R - column S as you wanted.
etc

 

[AWKodiak]

Thanks for the breakdown here. I was able to use the formula you gave.

 

[Peter_SSs]

Good news! Thanks for letting us know.