Complex formula repating

[Famousflynv]

I have a highly complex spreadsheet that I have to long hand the formula. Once I create the spreadsheet and create the formulas I cannot modify the sheet, only cell values. If I insert a column, the end formula will not count the new column contents (but I can delete columns before the formula and it will update the new column letters and they shift left). If I insert a row, I have to retype the entire formula. I can email anyone the spreadsheet if they would like to try. My goal is to be able to create more columns and rows that I may need, but not have to long hand the formula. Any help is appreciated! Typically I have between 5-25 assemblies and items can vary from 5-1,000!

Material Description <tbody> </tbody>	Assem 1 <tbody> </tbody>	Assem 2 <tbody> </tbody>	Assem 3 <tbody> </tbody>	Material Total <tbody> </tbody>
Assem quantity <tbody> </tbody>	1	2	3	BLANK OUT
Item A <tbody> </tbody>	1	4	7	30
Item B <tbody> </tbody>	2	5	8	36
Item C <tbody> </tbody>	3	6	9	42

<tbody>
</tbody>

Let's start with column E (Material Total). I will list the formulas so someone can copy and paint it into their own spreadsheet and figure it out.
Cell E3: =B3*B2+C3*C2+D3*D2+E3*E2+F3*F2+G3*G2+H3*H2+I3*I2
Cell E4: =B4*B2+C4*C2+D4*D2+E4*E2+F4*F2+G4*G2+H4*H2+I4*I2
Cell E5: =B5*B2+C5*C2+D5*D2+E5*E2+F5*F2+G5*G2+H5*H2+I5*I2

 

[Famousflynv]

HEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[shg]

From your example, I expect the formula in E3 should be

=SUMPRODUCT($B$2:$D$2, B3:D3)

Copy down.

	A​	B​	C​	D​	E​	F​
1​	Desc​	Assy 1​	Assy 2​	Assy 3​	Tot Matl​
2​	Assy Qty	1​	2​	3​
3​	Item A	1​	4​	7​	30​	E3: =SUMPRODUCT($B$2:$D$2, B3:D3)
4​	Item B	2​	5​	8​	36​
5​	Item C	3​	6​	9​	42​

 

[Famousflynv]

WOW! that worked out great. Can you explain why that worked out that way? $ means? etc?

 

[shg]

See Relative And Absolute Range References