Insert a column while maintaining row formulas

SewStage

New Member
Joined
Mar 16, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
ABCHIJKLMNO
1Product IDDescriptionCOGMAnimal eyes (sets)BattingButtonsCharm packDecorBond 809D/O ringsElasticFabric
2Avg $/Unit$2.13$9.16$1.49$10.72$5.50$4.49$0.79$11.00
3setyardeaeayard2/setyardyard
4ORG-01accessory organizer$ 5.93-2-0.11
5BOW-01bowl cozy$ 3.22-0.167
6CHX-01chickens$ 0.73-0.014
Material Usage
Cell Formulas
RangeFormula
C4:C6C4=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
NameRefers ToCells
MUBUTORG='Material Usage'!$J$4C4
MUFABBOW='Material Usage'!$O$5C5
MUFABCHK='Material Usage'!$O$6C6
MUFABORG='Material Usage'!$O$4C4
MUINTORG='Material Usage'!$X$4C4
MUMAGORG='Material Usage'!$AB$4C4
MUWALCHK='Material Usage'!$AU$6C6
MUWNZBOW='Material Usage'!$AW$5C5


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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,658
Office Version
  1. 365
Platform
  1. Windows
Since you have 365 won't this work for you ?
Excel Formula:
=-SUM(H4:AX4*H$2:AX$2)
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,109
Messages
5,768,148
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top