Remember Me?

#1
March 8th 13, 10:39 AM
 Junior Member First recorded activity by ExcelBanter: Feb 2013 Posts: 1
Linking data between sheets with limits on quantity

Hello

The Scenario:
the company I work for have four main customers who near the end of every month will e-mail a list of the stock they want, the maximum quantity they can accept and the price they can afford to pay per unit.

Say for example we have 28 units of one item and the customers have emailed us to saying this

customer 1 -- 10 units at £54.10/ea
customer 2 -- 100 units at £53.90/ea
customer 3 -- 15 units at £54.50/ea
customer 4-- 50 units at £52.50/ea

Now currently in the excel template I am trying to create I have a space for the total number of stock we have (in the example 28) and a two columns for each customer, one saying the price and another with the quantity. This could be a master style sheet and 4 more sheets each one with a blank invoice on it waiting for it to be populated by the stock they can pay the most for.

Obviously, as a company we want to sell as many as we can for the highest price so we would sell 15 to customer 3 for £54.50/ea, 10 to customer 1 for £54.10/ea and the remaining three to customer 2 for £53.90/ea

So my question is, how would I be able to make excel to rank the data so that the customer who is prepared to pay the most gets up to their maximum quantity and the rest (upto the max) goes to the other customers

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Patrick Johnson Links and Linking in Excel 0 December 20th 12 03:35 PM SPL Excel Discussion (Misc queries) 1 February 11th 09 06:39 PM SPL Excel Discussion (Misc queries) 0 February 11th 09 05:51 PM rudyeb Excel Discussion (Misc queries) 1 October 24th 08 03:34 PM April Excel Worksheet Functions 0 August 17th 07 02:19 AM

All times are GMT +1. The time now is 08:08 PM.