+ Reply to Thread
Results 1 to 14 of 14

Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    I have a table which holds al of my stock such as al the motherboards all of the RAM Ect... I will start with the motherboard list as an example. I need the motherboard drop-down list to look at the table of stock pic out only all of the motherboards and allow the user to choose from them. This will need to be done for each type of stock. So in the end there will be a motherboard drop-down list, a GPU drop-down list, a CPU drop-down list Ect... Thank you fro any advise and help I will supply my stock table so that if you find it easier you can work from this.

    Stock Table.xlsx
    Last edited by mdovey; 04-05-2014 at 04:20 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Where do you want the drop downs containing the unique lists of items?
    Regards,
    Rudi

  3. #3
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    It would be great to have it on a separate sheet at any location. I will move it to the correct place once I understand it. Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Hi,

    See the sample file attached. Not sure if this is what you are looking for...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Hello, You are edging towards what I am looking for. Just so I understand the 'Lists' sheet isn't automated? The category drop-down list is not needed as I am hoping to have it so there is a list for each type that you have shown there and they are all permanently set to e.g. motherboard and they can not be changed to anything else like you have done. I was hoping that the drop-down menus would be able to take the motherboards out from the stock table I have included before but I don't even know if that is plausible. If the 'Lists' sheet isn't automated I'm not sure my system will work as I'm not sure how i would get each product name to be placed into the table that you set up and for each one to be stored in the correct range. From what I can see you set it up manually and looked at my table and typed each one into the correct range.

  6. #6
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    The LIST sheet can be automated to accommodate changes in your source list. I just did not do this as I am still trying to determine exactly what you need and did not want to go through the efforts of programming it if it was not needed.

    Quoted: I am hoping to have it so there is a list for each type:
    Can you inform how you want these separate lists?
    Can it be one category on its own sheet or must they be all on the same sheet in lists one next to the other?

    Quoted: I was hoping that the drop-down menus would be able to take the motherboards out from the stock table
    I would not think that is a good idea as the lists are dependent on this source list.

    It would help if you can attach a sample workbook with the structure and layout of what you envision for these lists. I can use that and start to automate the extraction of the categories into these small lists so it suits your needs and ideas.

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Hello mdovey,

    What you are saying in your response to RudiS's reply in Post#4 is exactly what would be required. That is to keep it simplistic, without having to write elaborate Code to cater for same.

    @ RudiS,

    Well done, that is what I had in mind as well, but I just had to see what your reaction would be.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  8. #8
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Sorry I'm just finding it hard to explain. When i refer to lists I mean drop down lists. I have included what the drop down lists look like and how i intend to use them in this workbook. In the workbook I want to create an ordering system where i will be able to select which motherboards i want to sell. So under the motherboard section on the Hardware sheet I want drop down lists which allow me to select from my motherboards and under the CPU section I want to select the CPUs I want to sell through the drop down list.

    I was hoping originally the drop down lists would be populated from the stock table by pulling only the Motherboards for the motherboard drop-down list etc... But I'm not sure if thats possible so I tried making a new system which I have included called Back End stock. on this sheet I started separating all of the stock into a table for each product sold and then making the table the range of a drop down list e.g. the motherboard table would be the range for the motherboard drop down menus. I achieved most of this because on the larger scale which i haven't included I have set up a user from that allows you to add stock which originally only added it to the stock table. I then edited the code to also depending on the stock type added it to the table that holds that stock type e.g. If I add a motherboard it is stored in the stock table and the table on the back end sheet which only holds motherboards. I was told this was a long and messy way of going about it so I tried starting this thread to see if there was some code which would just pull only motherboards if it was the motherboard drop down list.

    Here is my workbook:
    Prototype Stock.xlsx

  9. #9
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Hi,

    See what you think of the options in the attached file.
    Pivot tables is a very easy and non programmatic way of maintaining and filtering for specific data.
    The button on the one sheet runs a macro to recreate the lists based on filtering the source.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Yes! Thank you, you have given me exactly what I was looking for even if it took me a while to fully explain what I wanted. The code that allows me to use the update button to fill the tables s excellent.

  11. #11
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Great. Good to know

  12. #12
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    Hmm running into a weird bug. I have extended the code to fit a larger range of products but i have kept it the same. The code you gave me for example takes all of the motherboards out of the stock table and puts it in the motherboard table on the Back End stock sheet with all of the details such as their names. This works correctly for all of my products and I have extended the code to work with 20 except 1. The GPU table correctly takes the GPUs out but t takes the name off all the stock in the stock table and adds it the name column for the GPU table in the back end sheet. Im very confused as I have looked over the code and it looks perfect so not sure why its only doing it for this 1 product type. Here is the code if you get the chance to look at it:

    The GPU code is highlighted in red for you

    Please Login or Register  to view this content.

  13. #13
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    I think your columns are overlapping?
    The majority of the tables have 9 column intervals where the RED references below have only 1 (AC>AD) and (AD>AL) is only 8.
    If the columns overlap on the pasted tables...the paste will not bring back the correct data block based on the internal Advanced filter that the macro is using.
    Make sure there is an interval of 9 or more columns between the table sets...determined by the references in red below.

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Drop-Down List Which Populates Its Self With Stock Belonging To A Specific Type

    So they do. thank you very much.

+ 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. Replies: 4
    Last Post: 02-18-2014, 07:58 PM
  2. Replies: 7
    Last Post: 02-06-2014, 07:40 AM
  3. [SOLVED] Drop down list that populates field
    By her.rockstar in forum Excel General
    Replies: 13
    Last Post: 01-21-2013, 01:36 PM
  4. Replies: 8
    Last Post: 09-22-2011, 03:47 PM
  5. type name and number populates in different cell
    By Aspctr in forum Excel General
    Replies: 2
    Last Post: 05-19-2010, 09:01 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