+ Reply to Thread
Results 1 to 3 of 3

Bill of Materials

  1. #1
    Registered User
    Join Date
    11-16-2007
    Posts
    2

    Bill of Materials

    On the attached spreadsheet I need to populate the BOM Creator tab with the details from the TOTAL tab. The TOTAL tab contains a bill of materials for a product but I need to present the data to populate an Access database as a list of values. I need all QTY values of each AssemblyID for every PlanarID listed. If the cell is blank I do not want it to return a zero value. AssyID is just an ascending autonumber value.

    The data will need to look like the following (/ indicates new column):

    AssyID/PlanarID/AssemblyID/QTY
    1/1/4/2
    2/1/181
    3/1/23/2
    4/1/26/1
    5/1/32/1
    6/1/37/1
    7/1/39/1

    Any help would be greatly appreciated!!!!!!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345
    Here are the formulas you need to populate your list. Just copy down some thousands of rows. You will get some #N/A errors for fields that don't exists. I just let them stay that way so you can identify them.

    When you have populated the list you can use copy - paste special to convert to values and then use autofilter to remove unwanted records. If this is a prosess you are going to repeate many times i guess you will want to find a more automatic way ?

    I had to insert a blank row 2 to get the MOD function to work properly.

    Hope my formulas will work all the way down, I just tried a few hundred rows....
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-16-2007
    Posts
    2

    Bom

    Thank you so much for the formulae, it has saved me 2000 lines of combo boxes! To remove N/A and blanks is no problem for me.

    We have this for several products so I shall try to replicate what you have done with those too. Thanks again.

+ 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