Need help subtracting daily from a total inventory amount that is replenished every so often

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
My workbook is broken into 3 separate sheets, #1 I am entering in a daily amount of different bundles and or products which each have their own cell. This string of data is essentially copied to sheet #2 and deleted from sheet #1 as sheet #1 's only responsibility is the daily input. Sheet 2 serves as the "database" and is updated daily. Sheet # 3 is where I need help.

Sheet #3 serves as the inventory tracker and needs to be depleted as sheet # 2 is updated. The range where the daily data in sheet 2 starts at is E3:O3 and R3:AJ3. Each of those 30 cells has a cell on sheet 3 that needs to be depleted via formula/VBA/anything dependent on what is updated for the corresponding column on sheet 2. I will post an example of what my sheet 2 and 3 look like.

Sheet 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2/5/2019[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/5/2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2/6/2019[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/6/2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Row 3/4 serve as examples of what the table will look like as it begins to generate. So again, for example row 3 column F, there would be 15 of product "a" going out. This would then be subtracted from a value on sheet 3 which follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bundle:[/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]b[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]c[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]d[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]e[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]f[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]g[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]h[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]i[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]j[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]


so back to the example before, because cell 3F on sheet 2 is 15 this would then subtract 15 from cell 4C on sheet 3.

Any help with this would be absolutely amazing, looking for a formula to plug into cells on sheet 3 to be able to do this if at all possible. Im very familiar with functions in excel and moderately familiar with VBA. Thank you in advance!
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,

Can you use this? Just adjust the ranges to suit your data;


Book1
EFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1DateabcdefghijDate123456789101112131415161718
202-05-1915346891011102-05-19001360014800011169
302-06-191467691111002-06-1900000014689752423
Sheet2



Book1
ABCDEFG
1
2StockQty AvailableStockQty Available
3Bundle:CountProductCount
4a1000984110001000
5b1000993210001000
6c100099031000999
7d100098741000997
8e100098651000994
9f1000982610001000
10g100098971000999
11h100098881000995
12i100099891000990
13j1000999101000984
14111000991
15121000993
16131000995
17141000997
18151000999
19161000995
20171000992
Sheet3
Cell Formulas
RangeFormula
C4=B4-SUMPRODUCT((Sheet2!$F$1:$O$1=Sheet3!A4)*Sheet2!$F$2:$O$3)
G4=F4-SUMPRODUCT((Sheet2!$S$1:$AJ$1=Sheet3!E4)*Sheet2!$S$2:$AJ$3)
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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