Forum Discussion

Wesjenks's avatar
Wesjenks
Copper Contributor
Jan 10, 2024

Formula Question, Autofill Question?

So my line of work uses a fairly simple tracking tool for production, yet every year we have to do an extension exercise with the data sorting portion. (weekly totaling, basic sum formulas)

I am asking to see if there is a simpler way rather than getting the entire column manually filled out and dragging it over to the right.

 

In the below example, the row for 44947 will need to next show =sum('DAILY INPUT'!B18:B24).

Is there a better formula or way to get the autofill or something to jump by 7 on each cell selected in the formula?

 

 

Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jan 10, 2024

    Wesjenks SEQUENCE() function will generate that number series jump by step 7. To sum from different sheet you will need INDIRECT() function. To make it dynamic spill array function use MAP() or BYROW() function. Spill array means you do not need to drag the formula manully. It will auto adjust formula when you enter new data to Column A. See the attached file for full formula.

    =MAP("'DailyInput'!B"&SEQUENCE(COUNTA(A4:A1000),1,4,7)&":"&"B"&SEQUENCE(COUNTA(A4:A1000),1,10,7),LAMBDA(x,SUM(INDIRECT(x))))

     

     

Share

Resources