bill of materials

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?

---