+ Reply to Thread
Results 1 to 8 of 8

Bill of Materials

  1. #1
    Registered User
    Join Date
    11-27-2017
    Location
    Seattle, WA
    MS-Off Ver
    2016
    Posts
    4

    Bill of Materials

    I created a list of parts we used to produce a Bill of Material for projects. The parts table is 140 rows long consisting of 7 columns. Engineers use this list to copy the row associated with the part number and paste it into a separate workbook to create the BOM row by row.

    I was asked to simplify the process by creating a drop down list on a form in a different worksheet so the engineers can select the part number and have the information filled in from the parts table. Using the same list I created a drop down list on a different worksheet and used VLOOKUP to complete the information in the other columns.

    The problem we have now is the parts list is expected to grow to 250 parts. Attempting to find one part among many has become challenging. I would like to create nested drop down lists. The first drop down list should be by manufacture (Cisco, Juniper, Dell, etc) which would limit the second drop down list to functions (switch, router, firewall, etc) which would limit the third drop down list to series (ASR, ISR) and the forth drop down would only list the part numbers that meet the criteria associated with the previous three selected items (Cisco/Router/ASR).

    All of the examples I have seen on various How To and Forums thus far have been very simple and required separate lists for each drop down list which would be burdensome to maintain as new devices are added to the various lists. Is there a better approach within Excel or have I reached the limits of what Excel can be used for?

    Respectfully

    Dennis

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Bill of Materials

    Attach example excel file (not a picture!) with BEFORE (what you have) and AFTER (what you want to achieve) sheets
    Desensitize data



    To attach an Excel file to your post,
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    11-27-2017
    Location
    Seattle, WA
    MS-Off Ver
    2016
    Posts
    4

    Re: Bill of Materials

    Hi Sandy,

    The attached is what we use today. The first tab shows the totals of the following tabs and provides the project totals at the bottom.
    Site Names 1 - 14 represent different phases or locations within the overall project.
    - Part Number / MFG / Function / Type / Description / Cost / QTY / Date / Total / Comments - are the needed columns.
    The 15th tab is the parts list

    As previously stated I would like to create nested drop down lists. The first drop down list should be by manufacture (Cisco, Juniper, Dell, etc) which would limit the second drop down list to functions (switch, router, firewall, etc) which would limit the third drop down list to series (ASR, ISR, etc) and the forth drop down would only list the part numbers that meet the criteria associated with the previous three selected items (Cisco/Router/ASR)

    In addition the columns need to be rearrange on the Site Name tabs to the following to make it easier to provide ordering information to vendors
    - MFG / Function / Type / PartNumber / Description / QTY / Total / Date / Comments

    Lastly... I would like to know if there is a function to have the tab name auto copied to the SiteTotals tab in the appropriate cell under Site Name.

    Dennis
    Attached Files Attached Files

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Bill of Materials

    First you need remove duplicates from PartNumber column and mistakes from others, like : Cisco vs CIsco.
    Maybe there is more errors but I don't want to work with mishmash data

  5. #5
    Registered User
    Join Date
    11-27-2017
    Location
    Seattle, WA
    MS-Off Ver
    2016
    Posts
    4

    Re: Bill of Materials

    There are some duplicate part numbers because they are associated with two different types. For example a license by be the same for an ASR1000 and 1000-X. I did find and resolve the typing mistakes. Given what you have seen is it possible to create the nested dropdown list ask I have described?

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Bill of Materials

    CAB-STACK-1M Cisco Switch CAB CISCO STACKWISE 1M STACKING CABLE FOR THE CATALYST 3750 $9,800.00 N/A
    CAB-STACK-1M Cisco Switch CAB CISCO STACKWISE 1M STACKING CABLE FOR THE CATALYST 3750 $9,900.00 N/A

    this is cheating !

    about your question - yes, it's possible but too much work.

    you can read here: dependent ddl or here: dependent ddl2 or Dependent Lists with Tables

    edit:
    but IMHO, you tryin' to reverse feature what you already have - filtering table
    Last edited by sandy666; 11-27-2017 at 02:12 PM.

  7. #7
    Registered User
    Join Date
    11-27-2017
    Location
    Seattle, WA
    MS-Off Ver
    2016
    Posts
    4

    Re: Bill of Materials

    Is there a better way to do this? I am okay doing the work or buying points to have it done for me.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Bill of Materials

    Check Table filter feature
    If you filter Mtg all the next filters show values assigned only to first choice, next if you select any value in Function, (so we have two filtered values) all the next filters show values assigned only to first and second selection, etc...

    You can try with Pivot Table but this is very similar to table filtering

    Maybe someone else will give solution with formula(s)

    edit:
    btw. you didn't show what you want to achieve

    on the end you may use Commercial Service
    Last edited by sandy666; 11-27-2017 at 06:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Condensing Bill of Materials
    By AndyStanton in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-23-2016, 12:11 PM
  2. BOM (Bill of Materials) Explosion
    By AndySimm2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2016, 06:06 AM
  3. Help with Designing a Bill of Materials!
    By blakewalker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2012, 11:52 AM
  4. Bill of materials form
    By JESSIER4025 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2010, 08:38 PM
  5. Bill of Materials
    By tonyhindmarsh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-19-2007, 05:37 AM
  6. bill of materials
    By stevekirk in forum Excel General
    Replies: 7
    Last Post: 12-10-2006, 05:12 PM
  7. [SOLVED] Bill of Materials / Router
    By MStim in forum Excel General
    Replies: 0
    Last Post: 09-14-2005, 10:05 AM

Tags for this Thread

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