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!
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: