+ Reply to Thread
Results 1 to 8 of 8

bill of materials

  1. #1
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723

    bill of materials

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote 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?

    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723
    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
    Last edited by stevekirk; 12-10-2006 at 08:11 AM.

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote 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?
    ---

  5. #5
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723
    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
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote 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
    Last edited by Bryan Hessey; 12-10-2006 at 10:20 AM.

  7. #7
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723
    byran,

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

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote 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?

    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1