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,082,167
Messages
5,363,528
Members
400,747
Latest member
monty_gl

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top