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!
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 287"]
<tbody>[TR]
[TD]Material Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]Assem 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]Assem 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]Assem 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]Material Total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 287"]
<tbody>[TR]
[TD]Assem quantity[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]BLANK OUT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 287"]
<tbody>[TR]
[TD]Item A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]7
[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][TABLE="width: 287"]
<tbody>[TR]
[TD]Item B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD][TABLE="width: 287"]
<tbody>[TR]
[TD]Item C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]42[/TD]
[/TR]
</tbody>[/TABLE]

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.

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Desc​

[/td][td]

Assy 1​

[/td][td]

Assy 2​

[/td][td]

Assy 3​

[/td][td]

Tot Matl​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Assy Qty[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]Item A[/td][td]

1​

[/td][td]

4​

[/td][td]

7​

[/td][td]

30​

[/td][td]E3: =SUMPRODUCT($B$2:$D$2, B3:D3)[/td][/tr]

[tr][td]

4​

[/td][td]Item B[/td][td]

2​

[/td][td]

5​

[/td][td]

8​

[/td][td]

36​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Item C[/td][td]

3​

[/td][td]

6​

[/td][td]

9​

[/td][td]

42​

[/td][td][/td][/tr]
[/table]

 

[Famousflynv]

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

 

[shg]

See Relative And Absolute Range References