+ Reply to Thread
Results 1 to 16 of 16

Excel 2007 : 2 drop-down lists - show only available

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Unhappy 2 drop-down lists - show only available

    Hi,I'm new here.I have a table with bots(Name,Size,Price,Category.....) And i have to do a Purchase Order.I wanna do 2 drop-down lists.The first with the names and the second with the sizez,but only sizes avalaible in the table.How could I do it??I've looked up on the internet the whole day and I didn't manage to do it.Thanks

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: 2 drop-down lists - show only available

    Hi & Welcome to the Forum,

    Maybe this will help...

    http://www.contextures.com/xlDataVal02.html
    HTH
    Regards, Jeff

  3. #3
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: 2 drop-down lists - show only available

    Hi,

    Do you mean certain names are only available in certain sizes OR you only want the second drop down to show those sizes that are currently available for each?

    If it's the first one then the link in Jeff's reply above has all the information you need.

  4. #4
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: 2 drop-down lists - show only available

    Spencer: Yes.I've got something like this:
    ADIDAS KANADIA 4 TR GTX M 42
    ADIDAS KANADIA 4 TR GTX M 42 1/3
    ADIDAS KANADIA 4 TR GTX M 43 1/3
    And when the buyer choose from the first list ADIDAS KANADIA 4 TR GTX M in the second i want only those three or more sizes.I don;t want them all
    jeffreybrown: I will try your link! Thanks.

  5. #5
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: 2 drop-down lists - show only available

    Then yes, the "dependent drop down list" approach using =INDIRECT() is what you need.

  6. #6
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: 2 drop-down lists - show only available

    Thanks for help,I'll try to do it.I hope I won't meet problems! :D

  7. #7
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: 2 drop-down lists - show only available

    I meet a problem .I've got those names too long .For Example: PUMA MOTORINA BALLERINA WNS,ADIDAS LIBRIA WINTER BOOT CP PL W,PUMA EPIC FLIP BEACH JAM
    .I dont know how to do those Name Boxes with such kind of names .

  8. #8
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: 2 drop-down lists - show only available

    You cannot use spaces in the range names so either have to combine them as one, PumaMotorinaBallerinaWns or Puma_Motorina_Ballerina_WNS.

    Hope that helps.

  9. #9
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: 2 drop-down lists - show only available

    it works now,i had to use SUBSTITUTE too but it works.And if i want do it with combo boxes i think i have to write in VBA the code.Am i right?

  10. #10
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: 2 drop-down lists - show only available

    I can see how SUBSTITUTE would be helpful in that. Well done

    Not sure about combo boxes I'm afraid. Not my area of expertise at all..

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: 2 drop-down lists - show only available

    Yes, if you want to use combo boxes in-lieu of the data validation, you'll need VBA code; however, IMO using data validation is much easier.

  12. #12
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: 2 drop-down lists - show only available

    And i've got a last questions :D ...How can i use those names : NIKE AIR* PEGASUS+ 26 (GS/PS) and PUMA 2,9 WN S SEASONAL in tha Name Box.It says that i have to enter a valid reference.

  13. #13
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: 2 drop-down lists - show only available

    You can type in Name Manager in Excel Help for further clarification.

    Learn about syntax rules for names
    The following is a list of syntax rules that you need to be aware of when you create and edit names.

    Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
    Note You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

    Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
    Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
    Name length A name can contain up to 255 characters.
    Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

  14. #14
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: 2 drop-down lists - show only available

    Jeffrey: I will have to use VBA code,because when i'll finish the Purchase Order and I will have to press button Order, the number of pieces from the list with the table have to decrease.I've got no idea how to do it! :D ..Sorry for double-post!

  15. #15
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: 2 drop-down lists - show only available

    Hi Piticu,

    I'm not going to be all that available for the next few hours, therefore, my recommendation.

    First, start a new thread (with an appropriate title) so you can get the most exposure for this question as possible. With this post count topping 14, not to many people will be looking at this thread.

    Second, it will benefit all involved if you attach a sample workbook with what you currently have and what you desire. Data validation may still be a viable option, but we never know until we see your true requiremnts.

    If you want to wait I would be happy to look back into this thread, but I can't promising anything right now.

    Finally, if you do create a new thread, kindly mark this one as solved.

  16. #16
    Registered User
    Join Date
    05-29-2012
    Location
    Unicov
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: 2 drop-down lists - show only available

    Ok,i will start a new thread when i will have to do the VBA code.thanks for help

+ 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