Dragging formula across multiple cells, but value only changing 1 at a time

[Babynod]

i have 5 columns for each day set up for my daily reporting. theres formulas in a few of the columns, i know if i drag a cell with a formula without locking it the value changes by 1. but when i try and drag 5 at once it changes the value by 5 places.

E.G in Cell ACR:9 it says 10-lines picked. this is pulling from another spreadsheet from IL:20

now when I highlight all 5 columns to drag them across, my ACW:9 cell I want to pull from IM20, but instead it goes to IQ20 because that's 5 columns across.

i need to copy this for everyday until the end of the financial year, and redoing it each time is out of the question

 

[Peter_SSs]

Thanks for the clarifications.

=SUM(INDEX('[1. Operations Dashboard.xlsx]AM Shift'!$IL111:$XFD119,0,(COLUMNS($ACR:ACR)+4)/5))

 

[Babynod]

peter mate your a legend.
thankyou very much

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Babynod]

Hi Peter, if your still around.
im having trouble with reducing the amount of columns im moving down to just every 2nd column
this is what ive tried - =SUM(INDEX(DRC!$NA11:$NE11,0,(COLUMN($DQ:DQ)+1)/2))

but it gives me the #REF! error. im trying to move it from DQ to DS. the cell im trying to move has =SUM(DRC!$NA11:$NE11) which shows around 13k value

i only want to move in groups of 2 this time. DQ/DR would move to DS/DT

 

[Peter_SSs]

I can't see what your formula is referring to or know what result(s) to expect. Can you give a small set of sample data from each worksheet and the expected results with XL2BB?

 

[Babynod]

havnt used XL2BB before but hopefully this works

Dragging formula accross 2 cells example data.xlsx
	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET
1		Jun-23 Week 3		Jun-23 Week 4		Jun-23 Week 5		Jul-23 Week 1		Jul-23 Week 2		Jul-23 Week 3		Jul-23 Week 4		Jul-23 Week 5		Aug-23 Week 1		Aug-23 Week 2		Aug-23 Week 3		Aug-23 Week 4
2
3
4		STATS %		STATS %		STATS %		STATS %		STATS %		STATS %		STATS %		STATS %		STATS %		STATS %		STATS %		STATS %
5
6
7
8
9	Expected Results	97978.0		119471.0	21.94%	122774.0	2.76%	13639.0	-88.89%
10
11	Lines Picked	97978.0		119471.0	21.94%
12
13
14
15
16
17
18		the STATS is the sum of the lines picked for the week.
19		the % is the difference between weeks
20		I have to extend this all the way to the end of the next FY (July 24)
21
22
23
24
25
26
27
28
29
30		DATA
31
32
33
34		10/06/2023	11/06/2023	12/06/2023	13/06/2023	14/06/2023	15/06/2023	16/06/2023	17/06/2023	18/06/2023	19/06/2023	20/06/2023	21/06/2023	22/06/2023	23/06/2023	24/06/2023	25/06/2023	26/06/2023	27/06/2023	28/06/2023	29/06/2023	30/06/2023	1/07/2023	2/07/2023	3/07/2023	4/07/2023	5/07/2023	6/07/2023	7/07/2023	8/07/2023	9/07/2023
35		Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
36		JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUN	JUL	JUL	JUL	JUL	JUL	JUL	JUL	JUL	JUL
37
38
39
40
41
42
43	Lines Picked				24178	29118	22829	21853			23065	29377	29109	20184	17736			24083	27103	28798	19883	22907			1843	4725	4865	2206
44
DRC Weekly Analysis

Cell Formulas
Range	Formula
DQ10	DQ10	=IF(SUM('https://axle.sharepoint.com/sites/GroupDCWarehouseOperations/Shared Documents/General/01. DCV/[001. Operations Dashboard.xlsx]DRC'!MQ10:MU10)=0,"",SUM('https://axle.sharepoint.com/sites/GroupDCWarehouseOperations/Shared Documents/General/01. DCV/[001. Operations Dashboard.xlsx]DRC'!MQ10:MU10))
DR10:DR11,DT10:DT11,DV10	DR10	=IFERROR((DQ10-DO10)/DO10,"")
DS10	DS10	=IF(SUM('https://axle.sharepoint.com/sites/GroupDCWarehouseOperations/Shared Documents/General/01. DCV/[001. Operations Dashboard.xlsx]DRC'!MX10:NB10)=0,"",SUM('https://axle.sharepoint.com/sites/GroupDCWarehouseOperations/Shared Documents/General/01. DCV/[001. Operations Dashboard.xlsx]DRC'!MX10:NB10))
DQ11	DQ11	=SUM(DS43:DW43)
DS11	DS11	=SUM(DZ43:ED43)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
ES37:ET43	Expression	=LEFT(ES$3,1)="S"	text	NO
EL37:EM43	Expression	=LEFT(EL$3,1)="S"	text	NO
DQ37:EK43	Expression	=LEFT(DQ$3,1)="S"	text	NO
DX11	Cell Value	beginning with ""	text	NO
DX11	Cell	does not contain a blank value	text	NO
DR1:DR3,DT1:DT3,DV1:DV3,DR5:DR12,DT5:DT12,DX5:DX10,DX1:DX3,DX12,DV5:DV12,DZ1:DZ3,EB1:EB3,ED1:ED3,EF1:EF3,EH1:EH3,EJ1:EJ3,EL1:EL3,EN1:EN3	Cell Value	beginning with ""	text	NO
DR1:DR3,DT1:DT3,DV1:DV3,DR5:DR12,DT5:DT12,DX5:DX10,DX1:DX3,DX12,DV5:DV12,DZ1:DZ3,EB1:EB3,ED1:ED3,EF1:EF3,EH1:EH3,EJ1:EJ3,EL1:EL3,EN1:EN3	Cell	does not contain a blank value	text	NO

 

[Peter_SSs]

Bit hard to follow locations, but try this in DQ. Again I'm not sure where your data ends so I just guessed at ZZ being far enough across.

=SUM(INDEX(DRC!$NA11:$ZZ11,(COLUMNS($DQ:DQ)+1)/2*7-6):INDEX(DRC!$NA11:$ZZ11,(COLUMNS($DQ:DQ)+1)/2*7-2))

Your DR formula should be fine.

 

[Babynod]

Bit hard to follow locations, but try this in DQ. Again I'm not sure where your data ends so I just guessed at ZZ being far enough across.

=SUM(INDEX(DRC!$NA11:$ZZ11,(COLUMNS($DQ:DQ)+1)/2*7-6):INDEX(DRC!$NA11:$ZZ11,(COLUMNS($DQ:DQ)+1)/2*7-2))

Your DR formula should be fine.

spot on again mate. can you explain the COLUMNS part, why you went /2*7-6 and /2*7-2 for the two different parts of the sum.
as if i can nail this i have a few other spreadsheets id like to have a crack on but they are different numbers of columns etc

 

[Peter_SSs]

can you explain the COLUMNS part, why you went /2*7-6 and /2*7-2

Each section that you want to add starts 7 columns after the last one so it has something to do with 7.
In the first formula column, DQ
(COLUMNS($DQ:DQ)+1)/2*7-6
(1+1)/2*7-6
2/2*7-6
=1
so the sum starts at cell 1 starting at col NA

(COLUMNS($DQ:DQ)+1)/2*7-2
(1+1)/2*7-2
2/2*7-2
=5
so the sum ends at cell 5 starting at col NA
The sum then is NA:NE as per your formula near the bottom of post #14

When the formula is in col DS
(COLUMNS($DQ:DS)+1)/2*7-6
(3+1)/2*7-6
=8
8th column starting at NA is NH

(COLUMNS($DQ:DS)+1)/2*7-2
(3+1)/2*7-2
=12
12th col starting at NA is NL
So that formula sums NH:NL
etc