Copying Formala across a columns in a spreadsheet usinga patern

[RMadden]

I have a formula in cell B7 being =IF(SUM(DataInput!M2*1)>0,DataInput!M2+0,"")), now I want to copy this formula across a range of columns but increasing the cell references by 4 each column across for example cell C7 should by =IF(SUM(DataInput!Q2*1)>0,DataInput!Q2+0,"") . As a side issue the "*1" and "+0" part of the equation was needed for excel to recognise the results as a number

 

[RMadden]

Okay I have read above and Fluff seems to have hit on a problem. First of all: -
1. Thanks re my coding issue - I will need to address that;
2. I will drop the XL2BB down below;
3. I tried the =LE as suggested and the options I got were "LEFT" and "LEN" not LET

StockOrderControl - updated.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Total	#NAME?	10	4
2	Count	0	1	1
3	Average	#NAME?	10	4
4		Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561	Brushcutter - FS560FS561
5		Mulching Blade	Tri Blade	Saw Blade 225mm FS560/FS460/FS561	Duro Cut Line - FS460/FS560/FS561	Locking Pin - Gear Head	Air Filter - FS560	Gear Head FS560	Spark Arrestor FS560FS561	Flange (Collar) nut - Nyloc	Rider Cup - Saw Blade FS560 FS561 FS460	Guard Washer
6		4000 713 3902	4000 713 4100	4000 713 4211	0000 930 3503	4130 893 7800	4148 141 0300	4148 640 0107	4148 140 6900	4116 642 7601	4119 713 3100	4110 717 2801
7	Aug 15, 2022	#NAME?			4		1				2
8	Aug 15, 2022	30	10			8	14		3	6	8
9	Aug 16, 2022											6
10	Aug 25, 2022	38		4			18		8	1
11	Aug 15, 2022				4		1				2
12
13
14
15
16
17
Sheet5

Cell Formulas
Range	Formula
B1:D1	B1	=SUM(B7:B1001)
B2	B2	=SUM(IF(ISERROR(DATEVALUE(TEXT(A7:A1000, "dd/MM/yyyy"))), 0, 1))
C2:D2	C2	=COUNTIF(C7:C1000,">0")
B3:D3	B3	=SUM(B1/B2)
C7:C17	C7	=IF(SUM(DataInput!Q2*1)>0,DataInput!Q2+0,"")
D7:D11	D7	=IF(SUM(DataInput!U2*1)>0,DataInput!U2+0,"")
E7:E17	E7	=IF(SUM(DataInput!Y2*1)>0,DataInput!Y2,"")
F7:F17	F7	=IF(SUM(DataInput!AC2*1)>0,DataInput!AC2,"")
G7:G17	G7	=IF(SUM(DataInput!AG2*1)>0,DataInput!AG2,"")
H7:H17	H7	=IF(SUM(DataInput!AK2*1)>0,DataInput!AK2,"")
I7:I17	I7	=IF(SUM(DataInput!AO2*1)>0,DataInput!AO2,"")
J7:J17	J7	=IF(SUM(DataInput!AS2*1)>0,DataInput!AS2,"")
K7:K17	K7	=IF(SUM(DataInput!AW2*1)>0,DataInput!AW2,"")
L7:L17	L7	=IF(SUM(DataInput!BA2*1)>0,DataInput!BA2,"")
D12:D17	D12	=IF(SUM(DataInput!U7*1)>0,DataInput!U7,"")
A7:A11	A7	=IF(DataInput!A2="","",DataInput!A2)
B7	B7	=LET(a,INDEX(DataInput!$M$2:$AZ$2,,COLUMNS($B7:B7)*4-3)*1,IF(ISERROR(a),"",IF(a>0,a,"")))
B8:B16	B8	=IF(SUM(DataInput!M3*1)>0,DataInput!M3+0,"")

 

[Peter_SSs]

. I tried the =LE as suggested and the options I got were "LEFT" and "LEN" not LET

So, you need to update. Look under File - Account - Update Options
Once updated, you should have the LET function.
If, for some reason, you do not want to do that or are are unable to do so, the 'full version' of Fluff's formula that I gave in post #6 should work for you.

 

[RMadden]

Thank you all. The formula now works for line 7, now I want to extend it down the page to row 1000. I removed the place holder as shown in the attached picture.
This seems to work okay.

 

[Peter_SSs]

Yes, that should be fine.

I'd still recommend getting the LET function if possible as you have quite a lot of those formulas and the LET version is more efficient.

 

[RMadden]

Thank you. From your typing fingers to the bosses pocket book.