bill of materials

Originally Posted by stevekirk

hi,
if i get an order for i item which contains 5 parts
i have a requirement to make 5 parts
if i book that item into stock the 5 parts are removed from stock
the 5 parts nor the i item are not removed from the requiements until the 1 item has been invoiced
therfore my pivot table always shows thwe requirements for the 5 items
is it possible to look at the 1 item see that it is in stock and remove the 5 items from the requirements

i have attached a file hopefully to explain what i am trying to do
example

very difficuilt to explian but hope this helps

steve

Hi,

This could be more a 'procedures' question than an Excel 'how to'

When you get a request for an item, the parts for that item are (theoretically if not actually) 'earmarked' (or reserved) for that item, so that anyone looking at stock levls will see the true available stock, not the stock as it was before the item was ordered.

To that end, I presume that column H is considered whenever column L stock is viewed, so that 'available' stock is viewed. For that I would have expected column M to show the reduced stock figure, but it shows the same value as column L.

The 'made' (ordered) part AND the parts required to make that part should not be on stock together, as this would show an inflated stock figure, so when you bring the 'made' part onto stock is the time that it's sub-parts should be removed. (or listed as 'used' in a spare column).

At what point is the made item taken onto stock? - can this be done in a UserForm so as to automate the process of reducing the sub-parts quantity?

Is this in the direction you are thinking?

---

bryan,

i thought this would be difficult

this data comes from the main frame and the report this data comes from is all outstanding orders by part number column a. the high level partnumber also shows all the lower parts as well and i have assinged the order number to it via the high level order number.

although the stock level has been adjusted for the piece parts the requirements will not change because we havwe not satisfied the order requirement.
what i am trying to do is create a form that shows all outstanding lower part requirments and the dates required

at present the pivot table shows all requirments and this is not the case because some of the requiremnts have gone into the high level part into stock

the stock levels for the part will always be the same but there is an allocated field for all orders

hope this helps

steve

Originally Posted by stevekirk

bryan,

i thought this would be difficult

this data comes from the main frame and the report this data comes from is all outstanding orders by part number column a. the high level partnumber also shows all the lower parts as well and i have assinged the order number to it via the high level order number.

although the stock level has been adjusted for the piece parts the requirements will not change because we havwe not satisfied the order requirement.
what i am trying to do is create a form that shows all outstanding lower part requirments and the dates required

at present the pivot table shows all requirments and this is not the case because some of the requiremnts have gone into the high level part into stock

the stock levels for the part will always be the same but there is an allocated field for all orders

hope this helps

steve

Steve,

I saw your note that the green showed 14 but only 12 were required, the decider being L16, where there is zero on stock.

OK testing some results in spare columns, (we can hide or remove them later)

in Q2

=IF(AND(E2<>"",L2=0),1,IF(E2<>"",0,Q1))

in R2

=IF(E2<>"",L2,IF(Q2=1,L2,L2-H2))

and fill those down a few rows.

anything like?
---

Originally Posted by stevekirk

BRYAN,

Not sure if it is working or not dont think so
look at the attached it will help better

it is one part used in 2 high level parts

total high level 124 of which 13 in stock
total number of the same lower part 124
one per high level
number of orders per lower level 124
i want the first 13 lower level order to be deleted as they are within the stock of the higher level part

steve

ok - thats a little different sheet, but, in Q2 put

=IF(MATCH("Description",A:A,0)>=ROW(A2),"",IF(ISERROR(VLOOKUP(D2,INDIRECT("F1:H"&MATCH("Description",A:A,0)),3,FALSE)),"",VLOOKUP(D2,INDIRECT("F1:H"&MATCH("Description",A:A,0)),3,FALSE)))

and fill that down, is that the 13 to remove?
---

added note, there are no spaces in that formula

Originally Posted by stevekirk

byran,

i did not copy the data correctly the description field is in column c
file changed
steve

Steve,

assuming you mean that the word 'Description' is in column C on the second heading as it is in the first, and that otherwise the columns are as shown, then in Q2 put

=IF(MATCH("Description",C$2:C$999,0)>=ROW(A2),"",IF(ISERROR(VLOOKUP(D2,INDIRECT("F1:H"&MATCH("Description",C$2:C$999,0)),3,FALSE)),"",VLOOKUP(D2,INDIRECT("F1:H"&MATCH("Description",C$2:C$999,0)),3,FALSE)))

Is that the 13 you seek?

---