Insert a column while maintaining row formulas

[SewStage]

I searched for previous questions on this but wasn't able to find exactly what I'm looking for. I have a tedious formula in column c (and if anyone has a solution for *this* I'd be forever grateful as well) that gets messed up every time I insert a new column - for example if I insert (insert or copy/insert copied cells) a column before column K, the formula for K disappears:

Before: =SUM((-1*H4)*$H$2)+((-1*I4)*$I$2)+((-1*J4)*$J$2)+((-1*K4)*$K$2)+((-1*L4)*$L$2)
After: =SUM((-1*H4)*$H$2)+((-1*I4)*$I$2)+((-1*J4)*$J$2)+((-1*L4)*$L$2)

2021 Inventory Management.xlsx
	A	B	C					H	I	J	K	L	M	N	O
1	Product ID	Description	COGM					Animal eyes (sets)	Batting	Buttons	Charm pack	DecorBond 809	D/O rings	Elastic	Fabric
2	Avg $/Unit							$2.13	$9.16	$1.49	$10.72	$5.50	$4.49	$0.79	$11.00
3								set	yard	ea	ea	yard	2/set	yard	yard
4	ORG-01	accessory organizer	$ 5.93							-2					-0.11
5	BOW-01	bowl cozy	$ 3.22												-0.167
6	CHX-01	chickens	$ 0.73												-0.014
Material Usage

Cell Formulas
Range	Formula
C4:C6	C4	=SUM((-1*H4)*$H$2)+((-1*I4)*$I$2)+((-1*J4)*$J$2)+((-1*K4)*$K$2)+((-1*L4)*$L$2)+((-1*M4)*$M$2)+((-1*N4)*$N$2)+((-1*O4)*$O$2)+((-1*P4)*$P$2)+((-1*Q4)*$Q$2)+((-1*R4)*$R$2)+((-1*S4)*$S$2)+((-1*T4)*T4)+((-1*U4)*$U$2)+((-1*V4)*$V$2)+((-1*W4)*$W$2)+((-1*X4)*$X$2)+((-1*Y4)*$Y$2)+((-1*Z4)*$Z$2)+((-1*AA4)*$AA$2)+((-1*AB4)*$AB$2)+((-1*AC4)*$AC$2)+((-1*AD4)*$AD$2)+((-1*AE4)*$AE$2)+((-1*AF4)*$AF$2)+((-1*$AG$4)*$AG$2)+((-1*AH4)*$AH$2)+((-1*AI4)*$AI$2)+((-1*AJ4)*$AJ$2)+((-1*AK4)*$AK$2)+((-1*AL4)*$AL$2)+((-1*AM4)*$AM$2)+((-1*AN4)*$AN$2)+((-1*AO4)*$AO$2)+((-1*AP4)*$AP$2)+((-1*AQ4)*$AQ$2)+((-1*AR4)*$AR$2)+((-1*AS4)*$AS$2)+((-1*AT4)*$AT$2)+((-1*AU4)*$AU$2)+((-1*AV4)*$AV$2)+((-1*AW4)*$AW$2)+((-1*AX4)*$AX$2)

Named Ranges
Name	Refers To	Cells
MUBUTORG	='Material Usage'!$J$4	C4
MUFABBOW	='Material Usage'!$O$5	C5
MUFABCHK	='Material Usage'!$O$6	C6
MUFABORG	='Material Usage'!$O$4	C4
MUINTORG	='Material Usage'!$X$4	C4
MUMAGORG	='Material Usage'!$AB$4	C4
MUWALCHK	='Material Usage'!$AU$6	C6
MUWNZBOW	='Material Usage'!$AW$5	C5

I've tried doing this as a table and get the same result. Is there a way to ensure the formulas don't get messed up when inserting new columns, as I need to do this pretty frequently?

Thanks much!

 

[Alex Blakenburg]

Since you have 365 won't this work for you ?

=-SUM(H4:AX4*H$2:AX$2)

 

[SewStage]

Since you have 365 won't this work for you ?

=-SUM(H4:AX4*H$2:AX$2)

Yes, that worked perfectly, thank you so much! I didn't know you could do a "-SUM like that.

 

[Alex Blakenburg]

Glad it worked for you. Thanks for letting me know.