Question

Formula autofill help

Hello, I m learning excel and for work I need to combined large chunks of data from one sheet to another. I came up with the formula =SUM( 940748 SB Class 05-08-23 !F1:F12) in the second sheet for the first batch of data from sheet one. The problem I am having is when fill down the formula I need the cell range to increase by increments of 12 not 1, so F1:F12 becomes F13,F24 when I fill down one row. My knowledge of excel is very limited so I have no clue on were to even begin to get this done.

Answer 1

INDIRECT() as well as INDEX() will work. I suggest to use non volatile function INDEX(). Try-

=SUM(INDEX(Sheet2!A:A,ROW($A1)*12-11):INDEX(Sheet2!A:A,ROW($A1)*12))

For Microsoft-365 can try below formula if your data is continuous.

=BYROW(WRAPROWS(TOCOL(Sheet2!A:A,1),12,0),
LAMBDA(x,SUM(x)))

友情链接