Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula Question, Autofill Question?

Copper Contributor

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?

 

Wesjenks_0-1704844731646.png

 

1 Reply

@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))))

Harun24HR_0-1704856008240.png