Automatically tracking how much material is left

k3v1n23

New Member
Joined
May 13, 2013
Messages
7
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:
World MapMediumBlack - (M)

<tbody>
</tbody>
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

New Member
Joined
May 13, 2013
Messages
7
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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.
 

Forum statistics

Threads
1,085,984
Messages
5,387,105
Members
402,041
Latest member
sfb2020

Some videos you may like

This Week's Hot Topics

Top