| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring out inventory levels
Help!
I have a 3 page spreadsheet that I need to maintain. The first page is a table that basically has part number, attribute A, attribute B and starting inventory level. Attributes A and B are unrelated to this exercise and can be ignored. Page 2 has all of the customer orders, the only 2 cells that matter are are part number and quantity. On page 3 I would like to have a list with each of the part numbers and the current inventory level. Theoretically, I need a formula like a vlookup on page 3 that will look at a value (the part number), find the initial starting inventory from page one, and then subtract all of the quantities for that part on page 2. The net number should be inventory on hand. This sounds simple but I am racking my brain trying to figure it out. The second page |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring out inventory levels
hi
example(I simplflied for test) part number on sheet1 A2 Start Qty on sheet1 B2 -------------------------- customer on sheet 2 A2 Part ordered on sheet2 B2 Qty ordered on sheet2 C2 ------------------------------ part number on sheet3 A2(same as sheet1 & same cell 2) Current QOH on sheet3 B2 = =Sheet1!B2-SUMIF(Sheet2!B2:B6,Sheet1!A2,Sheet2!C2:C6) adjust to suit. regards FSt1 "JSF" wrote: Help! I have a 3 page spreadsheet that I need to maintain. The first page is a table that basically has part number, attribute A, attribute B and starting inventory level. Attributes A and B are unrelated to this exercise and can be ignored. Page 2 has all of the customer orders, the only 2 cells that matter are are part number and quantity. On page 3 I would like to have a list with each of the part numbers and the current inventory level. Theoretically, I need a formula like a vlookup on page 3 that will look at a value (the part number), find the initial starting inventory from page one, and then subtract all of the quantities for that part on page 2. The net number should be inventory on hand. This sounds simple but I am racking my brain trying to figure it out. The second page |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring out inventory levels
forgot to mention.
you could probably put the QOH on sheet 1 and avoid duplication of data and file bloat. my thoughts FSt1 "FSt1" wrote: hi example(I simplflied for test) part number on sheet1 A2 Start Qty on sheet1 B2 -------------------------- customer on sheet 2 A2 Part ordered on sheet2 B2 Qty ordered on sheet2 C2 ------------------------------ part number on sheet3 A2(same as sheet1 & same cell 2) Current QOH on sheet3 B2 = =Sheet1!B2-SUMIF(Sheet2!B2:B6,Sheet1!A2,Sheet2!C2:C6) adjust to suit. regards FSt1 "JSF" wrote: Help! I have a 3 page spreadsheet that I need to maintain. The first page is a table that basically has part number, attribute A, attribute B and starting inventory level. Attributes A and B are unrelated to this exercise and can be ignored. Page 2 has all of the customer orders, the only 2 cells that matter are are part number and quantity. On page 3 I would like to have a list with each of the part numbers and the current inventory level. Theoretically, I need a formula like a vlookup on page 3 that will look at a value (the part number), find the initial starting inventory from page one, and then subtract all of the quantities for that part on page 2. The net number should be inventory on hand. This sounds simple but I am racking my brain trying to figure it out. The second page |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Figuring Age | Excel Worksheet Functions | |||
| Figuring out a formula | Excel Worksheet Functions | |||
| Pivot Table For Running Inventory Levels | Excel Discussion (Misc queries) | |||
| Figuring Grades | Excel Worksheet Functions | |||
| How can i get an inventory list that adds and subtracts inventory | Excel Discussion (Misc queries) | |||
Linear Mode
