Streamlining a long, manual formula

S

SewStage

Board Regular
Joined
Mar 16, 2021
Messages
70
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that does a number of things including cost and inventory tracking. In a nutshell, this tab (Material by Mo) adds any (raw) material purchases to the starting or previous month's quantity and subtracts the specified quantities (Material Usage) for the number of products completed each month (Product Completion by Mo). Close to 90% of everything I turn out involves fabric, so the calculation for that is long, and every time I add a new product I have to manually go into each raw material used in the product and update the Material by Mo tab by adding another calculation of "+(Product Completion by Mo xxx * Material Usage yyy)". I'm wondering if there's a way to streamline this to the extent that I can have a single formula for everything on the Material by Mo tab, so when I add a new product I can just copy/paste from the previous row. I've included a few rows from each of the tabs involved in the Material by Mo calculations. I've also been playing around a bit with =INDEX but I just don't comprehend it enough to be successful yet. Any thoughts on this (other than VBA/coding) would be very much appreciated!

PLAYGROUND.xlsx
ABCDEFGHIJKLMNO
1Item DescriptionUOM31-DecJanFebMarAprMayJunJulAugSepOctNovDec
2Animal eyes (sets)set384141          
3Battingyard000          
4Buttonsea103103103          
5Charm packea373737          
6Corkyard666          
7DecorBond 809 yard222222          
8D/O ringsset/2303030          
9Elasticyard126126126          
10Fabricyard914914914          
Material by Mo.
Cell Formulas
RangeFormula
D2:O2D2=IF(MONTH(D1)<=MONTH(TODAY()),C2+SUMIF(PPIVITEM,$A$2,PPIVCOUNT)+(MUEYESFCP*PCBMFCP)+(MUEYESFJR*PCBMFJR),"")
D3:O3D3=IF(MONTH(D1)<=MONTH(TODAY()),C3+SUMIF(PPIVITEM,$A$3,PPIVCOUNT)+(MUBATQ1*PCBMQ1)+(MUBATQ2*PCBMQ2)+(MUBATQ3*PCBMQ3)+(MUBATQ4*PCBMQ4)+(MUBATQ5*PCBMQ5),"")
D4:O4D4=IF(MONTH(D1)<=MONTH(TODAY()),C4+SUMIF('Purchases pivot table'!$A$1:$A$150,$A$4,'Purchases pivot table'!B$1:B$150)+('Product Completion by Mo.'!D2*'Material Usage'!$H$4)+('Product Completion by Mo.'!D20*'Material Usage'!$H$22),"")
D5:O5D5=IF(MONTH(D1)<=MONTH(TODAY()),C5+SUMIF('Purchases pivot table'!$A$1:$A$150,$A$5,'Purchases pivot table'!B$1:B$150)+('Product Completion by Mo.'!D9*'Material Usage'!$I$11),"")
D6:O6D6=IF(MONTH(D$1)<=MONTH(TODAY()),C6+SUMIF(PPIVITEM,$A$6,PPIVCOUNT),"")
D7:O7D7=IF(MONTH(D1)<=MONTH(TODAY()),C7+SUMIF(PPIVITEM,$A$7,PPIVCOUNT),"")
D8:O8D8=IF(MONTH(D1)<=MONTH(TODAY()),C8+SUMIF('Purchases pivot table'!$A$1:$A$150,$A$8,'Purchases pivot table'!B$1:B$150)+('Product Completion by Mo.'!D$14*'Material Usage'!$L$16),"")
D9:O9D9=IF(MONTH(D1)<=MONTH(TODAY()),C9+SUMIF('Purchases pivot table'!$A$1:$A$150,$A$9,'Purchases pivot table'!B$1:B$150),"")
D10:O10D10=IF(MONTH(D1)<=MONTH(TODAY()),C10+SUMIF('Purchases pivot table'!$A$1:$A$150,$A$10,'Purchases pivot table'!B$1:B$150)+('Product Completion by Mo.'!D$3*'Material Usage'!$N$5)+('Product Completion by Mo.'!D$4*'Material Usage'!$N$6)+('Product Completion by Mo.'!D$8*'Material Usage'!$N$10)+('Product Completion by Mo.'!D$9*'Material Usage'!$N$11)+('Product Completion by Mo.'!D$10*'Material Usage'!$N$12)+('Product Completion by Mo.'!D$14*'Material Usage'!$N$16)+('Product Completion by Mo.'!D$15*'Material Usage'!$N$17)+('Product Completion by Mo.'!D$21*'Material Usage'!$N$23)+('Product Completion by Mo.'!D$22*'Material Usage'!$N$24)+('Product Completion by Mo.'!D$23*'Material Usage'!$N$25)+('Product Completion by Mo.'!D$44*'Material Usage'!$N$44)+('Product Completion by Mo.'!D$45*'Material Usage'!$N$45)+('Product Completion by Mo.'!D$46*'Material Usage'!$N$46)+('Product Completion by Mo.'!D$47*'Material Usage'!$N$47),"")
Named Ranges
NameRefers ToCells
PPIVCOUNT='Purchases pivot table'!B$5:B$150D6

PLAYGROUND.xlsx
ABCDEFGHIJKLMN
1Product IDDescriptionCOGMWholesale PriceRetail PriceAnimal eyes (sets)BattingButtonsCharm packCorkDecorBond 809D/O ringsElasticFabric
2Avg $/Unit$0.50$11.95$0.18$13.99$50.81$3.99$1.22$0.83$10.49
3setyardeaeayardyard2/setyardyard
4ORG-01accessory organizer$ 3.61$ 30.00$ 42.00-2-0.11
5BOW-01bowl cozy$ 2.53$ 8.00-0.167
6CHX-01chickens$ 0.39$ 5.00$ 5.00-0.014
7CNB-01covered notebook$ 8.02$ 16.00$ 16.00-0.25-0.333
8CBB-CRKcrossbody bag - cork$ 37.45-0.333-0.333
9CBB-CTNcrossbody bag - cotton$ 28.24-0.083-0.666
10DRW-MDdrawstring project bag$ 10.73$ 20.00$ 30.00-0.75
11FCP-01froggy charm pack$ 16.61$ 25.00-1-0.38-0.25
Material Usage
Cell Formulas
RangeFormula
F2F2=IFERROR(AVERAGEIF(DESC,F$1,UNITCOST),'Material by Mo.'!$Q2)
G2G2=IFERROR(AVERAGEIF(DESC,G$1,UNITCOST),'Material by Mo.'!$Q3)
H2H2=IFERROR(AVERAGEIF(DESC,H$1,UNITCOST),'Material by Mo.'!$Q4)
I2I2=IFERROR(AVERAGEIF(DESC,I$1,UNITCOST),'Material by Mo.'!$Q5)
J2J2=IFERROR(AVERAGEIF(DESC,J$1,UNITCOST),'Material by Mo.'!$Q6)
K2K2=IFERROR(AVERAGEIF(DESC,K$1,UNITCOST),'Material by Mo.'!$Q7)
L2L2=IFERROR(AVERAGEIF(DESC,L$1,UNITCOST),'Material by Mo.'!$Q8)
M2M2=IFERROR(AVERAGEIF(DESC,M$1,UNITCOST),'Material by Mo.'!$Q9)
N2N2=IFERROR(AVERAGEIF(DESC,N$1,UNITCOST),'Material by Mo.'!$Q10)
C4:C11C4=-SUM(F4:BZ4*$F$2:$BZ$2)
Named Ranges
NameRefers ToCells
DESC='Material purchases'!$B$14:$B$1997F2:N2
MU809CNB='Material Usage'!$K$7C7
MUBUTORG='Material Usage'!$H$4C4
MUCPFCP='Material Usage'!$I$11C11
MUEYESFCP='Material Usage'!$F$11C11
MUFABBOW='Material Usage'!$N$5C5
MUFABCHK='Material Usage'!$N$6C6
MUFABCNB='Material Usage'!$N$7C7
MUFABDRW='Material Usage'!$N$10C10
MUFABFCP='Material Usage'!$N$11C11
MUFABORG='Material Usage'!$N$4C4
MUFLCDRW='Material Usage'!$Q$10C10
MUFLCFCP='Material Usage'!$Q$11C11
MUINTCNB='Material Usage'!$W$7C7
MUINTORG='Material Usage'!$W$4C4
MUMAGORG='Material Usage'!$AA$4C4
MUNTBK='Material Usage'!$AE$7C7
MUPPFCP='Material Usage'!$AH$11C11
MURIBCNB='Material Usage'!$AL$7C7
MURIBDRW='Material Usage'!$AL$10C10
MUWALCHK='Material Usage'!$AV$6C6
MUWNZBOW='Material Usage'!$AX$5C5
UNITCOST='Material purchases'!$F$14:$F$1997F2:N2

PLAYGROUND.xlsx
ABCDEFGHIJKLMNO
1Product IDItem DescriptionBeginning QtyJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
2ORG-01accessory organizer0
3BOW-01bowl cozy61
4CHK-01chickens16
5CNB-01covered notebook8
6CBB-CRKcrossbody bag - cork0
7CBB-CTNcrossbody bag - cotton0
8DRW-MDdrawstring project bag14
9FCP-01froggy charm pack21
Product Completion by Mo.

PLAYGROUND.xlsx
ABCDEFGH
1CategoryDescriptionDetails Cost UnitsPer Unit CostCompanyDate of Purchase
14NotionsAnimal eyes (sets)$ 2.004$ 0.501/12/2022
Material purchases
Cell Formulas
RangeFormula
F14F14=IF(E14>0,D14/E14,"")
Named Ranges
NameRefers ToCells
COST='Material purchases'!$D$14:$D$1997F14

PLAYGROUND.xlsx
ABCDEFGHIJKLMN
1Yr of Purchase2022
2JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
3Sum of Units
4123456789101112Grand Total
5Animal eyes (sets)44
6(blank)
7Grand Total44
Purchases pivot table
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
I've tried this, and though it's not actually returning the correct value (it does add the purchases from the SUMIF statement) it's also no longer throwing me errors; am I on the right track with using indexing?? =IF(MONTH(D1)<=MONTH(TODAY()),C2+SUMIF(PPIVITEM,$A$2,PPIVCOUNT)+(INDEX('Product Completion by Mo.'!$D$2:$O$26,ROWS(D$2:D2),1)*INDEX('Material Usage'!$F$4:$BZ$28,ROWS(F$4:F4),1)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
K
  • Question Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
517
kcampbell675
K
B
  • Question Question
GETPIVOTDATA Sum of multiple results
Replies
5
Views
326
BlitzTwig
B
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
258
Alex Blakenburg
A
R
  • Solved
Need help with a formula
Replies
4
Views
304
Fluff
Fluff

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top