Lookup Question (Tricky)

good day people,

i have a very tircky question to ask, its most trick question i ever came acroos and i have spent alot of time on it but could'nt fix it

i have attached a sample sheet my orgnal sheet consist 100 of tabs and thousands of rows of data what i want from this ffile is below.

this file used for production Planing purpose, first tab is my product tab where i have different catagoris of the same product all the products are made from (Bot+Cap+UCN), i like to stream line my purchase system as our ordering never balanced, so we will use the UCN quantity as the measuring para meter, here is the question

1-I want to get customers orders individualy by the no in front of every product, they shud reflect in the table below of all other tables, col heading shud be the customer name, then it shud match custooemr name and product code and return the value in the cross ponding cell, such as if more then three items are using black cap it shud sum up all the cap cap related UCN and subtract it from the cap value to get the difference

2- Caps ordering, same color caps are used in different products so it sum up all the identical caps and subtract them the total no of crossponding UCN then get the difference in the desired col

3- unit carton stock falls below 500 a automatis order shud be made, if fast moving then 10,000 other wise 5,000. assesment of fast moving item cud be obtained from the third tab.

i have also attached the sample file please do need full i will be much appratiated.

thansk in advance

Usman
sample file.xlsx

Hi usmanfarok,

suggest you to edit the thread title to reflect the query you asked..see the forum rules:-

http://www.excelforum.com/forum-rule...rum-rules.html

Regards,
DILIPandey

<click on below 'star' if this helps>