A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Links and Linking in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Linking data between sheets with limits on quantity



 
 
Thread Tools Display Modes
  #1  
Old March 8th 13, 11:39 AM
owj1993 owj1993 is offline
Junior Member
 
First recorded activity by ExcelBanter: Feb 2013
Posts: 1
Exclamation 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


Greatly appreciate your help guys
Ads
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking From raw data sheet to sub sheets Patrick Johnson Links and Linking in Excel 0 December 20th 12 04:35 PM
linking data between sheets SPL Excel Discussion (Misc queries) 1 February 11th 09 07:39 PM
linking data to specific sheets SPL Excel Discussion (Misc queries) 0 February 11th 09 06:51 PM
Need a formula to increase quantity of one cell until the quantity ofanother is exceeded rudyeb Excel Discussion (Misc queries) 1 October 24th 08 03:34 PM
Linking data in workbook to its sheets April Excel Worksheet Functions 0 August 17th 07 02:19 AM


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


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.