Deplete cell values based on a common reference 2

[victoria2207]

Good Afternoon,

I posted a similar query last month and got a great response, I now have to expand upon the idea.

Basically, I have a stock sheet and every time a product is created the stock for it must be depleted manually a part at a time. We have almost 8000 parts and each product can consist of up to 500 different items.

I have created a monthly stock sheet with a column for each day of the month OR product created:

[TABLE="width: 442"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="141" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5006;"> <col width="64" style="width: 48pt;" span="5"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 141, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Date Created[/TD]
[TD="class: xl67, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl68, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl68, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl68, width: 64, bgcolor: #D9E1F2"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Work Order[/TD]
[TD="class: xl69, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl70, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl70, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl70, width: 64, bgcolor: #D9E1F2"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Name[/TD]
[TD="class: xl71, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #D9E1F2"] [/TD]
[/TR]
[TR]
[TD="class: xl73, width: 64, bgcolor: transparent"]Part No.[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Supplier Part No.[/TD]
[TD="class: xl74, width: 141, bgcolor: transparent"]Description[/TD]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]TOTAL Outbound[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]Item 1[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]Item 2 [/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]Item 3[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]Etc[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]AAA[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]6704[/TD]
[TD="class: xl78, bgcolor: transparent"]Washer[/TD]
[TD="class: xl79, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0[/TD]
[TD="class: xl80, bgcolor: white"] [/TD]
[TD="class: xl80, bgcolor: white"] [/TD]
[TD="class: xl80, bgcolor: white"] [/TD]
[TD="class: xl80, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]BBB[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]10039[/TD]
[TD="class: xl81, bgcolor: transparent"]Clamp[/TD]
[TD="class: xl82, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0[/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]CCC[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]10612[/TD]
[TD="class: xl81, bgcolor: transparent"]Mount[/TD]
[TD="class: xl82, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0[/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]DDD[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]10617[/TD]
[TD="class: xl81, bgcolor: transparent"]Filter[/TD]
[TD="class: xl82, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0[/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Etc[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl82, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0[/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[TD="class: xl83, bgcolor: white"] [/TD]
[/TR]
</tbody>[/TABLE]


What I think I would like to be able to do is when a material list is dropped into a sheet (Say the 'work order' sheet), at the top of each column have a button which runs a macro which will look up to that sheet for the values (which will have the part number in common) and drop the values into that particular column then copy/paste them as values.

There may be an easier way to do this, including an individual sheet for each product (and I can simply do a sumif) but this has 2 problems:

1. The file will be massive - this is already a problem with the current stock file which has to be repaired on average once a month
2. The other people using it are not as familiar with Excel as me (and I am far/far from an expert) so asking them to enter formula without causing an error somewhere is far from ideal.

If anyone has any ideas/examples of something that already exists that fits this purpose - I would be very greatful.

Thanks in advance,

Vic2207

 

[victoria2207]

Using the VBA provided to me in my previous thread and tailoring it, I have managed to resolve this.
Thank you to anyone who considered this query.