Automatically tracking how much material is left

[k3v1n23]

Hi guys,

Please refer to screenshots below for reference.

So basically I want to be able to keep track of how much vinyl material I have left after each order.

The process would be - When I order a roll of vinyl material I would input the colour ordered and cm ordered by selecting from drop down lists in the 'Vinyl Tracker' sheet. When a customer makes an order, I would select an item, size and colour from drop down lists in the 'Orders' sheet. Depending on what size is selected in the 'Orders' sheet, I would then like Excel to automatically update column 'Cm Remaining' in the 'Vinyl Tracker' sheet, however using the smaller number in the relative size column from 'Item Sizes' sheet.

E.g. If we take the first order:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]World Map[/TD]
[TD="align: center"]Medium[/TD]
[TD="align: center"]Black - (M)[/TD]
[/TR]
</tbody>[/TABLE]
This would then refer to cell M5 in the 'Item Sizes' sheet (as 43.35 is less than 90).

I would then like the number which is retrieved to be taken away from the relevant cell in the 'Cm Ordered' column, depending on what colour was chosen in the 'Orders' sheet.

To make matters a bit more complex, obviously when any of the numbers in the 'Cm Ordered' column in sheet 'Vinyl Tracker' is 0, I will re-order the same vinyl roll and insert it into the sheet as per usual. How can I make it so that any new orders will take away from the latest instance of the same coloured vinyl?

I apologize in advance if this is hard to make sense of (just about makes sense in my head!).


Many thanks in advance,

Kev

Orders Sheet

Item Sizes Sheet

Vinyl Tracker Sheet

 

[k3v1n23]

Sorry, when I mentioned near the end about re-ordering the same vinyl, I was meant to say ' Cm Remaining' not 'Cm Ordered' as being 0 in sheet 'Vinyl Tracker'.

 

[konew1]

I think you need to explain more carefully.
My first question: What is the relevance of the 2 columns of numbers for each colour on the 'Item Sizes' sheet?
Your posting has been up for some considerable time and nobody has been able to understand exactly what you need. We don't know about your business and you need to explain.