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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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

K
  • Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
336
kcampbell675
K
R
  • Solved
Need help with a formula
Replies
4
Views
108
Fluff
Fluff
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
898
Joe4
Joe4
M
  • Question
Workbook design question on large datasets and formulas
Replies
2
Views
404
Mr_Phil
M
deadlyjack
  • Solved
Dynamic INDEX/MATCH-function with 2 value-identities
Replies
8
Views
672
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,386
Messages
6,119,216
Members
448,876
Latest member
Solitario

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