Results 1 to 9 of 9

Thread: Excel Drop down box

  1. #1
    Platinum Member sterne.law@gmail.com's Avatar
    Join Date
    Oct 2009
    Location
    Durban
    Posts
    1,328
    Thanks
    38
    Thanked 561 Times in 410 Posts
    Blog Entries
    7

    Excel Drop down box

    I want to use the dropdown box in my excel, I cant for the life of me remember how.
    I want the dropdown to show the populated list so that the exact word can be chosen.

    if its a plug in any links to same would be appreciated.
    Anthony Sterne

    www.acumenholdings.co.za
    DISCLAIMER The above is merely a comment in discussion form and an open public arena. It does not constitute a legal opinion or professional advice in any manner or form.

  2. #2
    Diamond Member adrianh's Avatar
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    5,089
    Thanks
    336
    Thanked 808 Times in 642 Posts
    How easily someone is offended is directly proportional to how stupid they are.
    ~GS Elevator

  3. #3
    Platinum Member sterne.law@gmail.com's Avatar
    Join Date
    Oct 2009
    Location
    Durban
    Posts
    1,328
    Thanks
    38
    Thanked 561 Times in 410 Posts
    Blog Entries
    7
    :

    Man have I been meaning to incorporate this feature for ages, no idea what took me so long to do it.
    Anthony Sterne

    www.acumenholdings.co.za
    DISCLAIMER The above is merely a comment in discussion form and an open public arena. It does not constitute a legal opinion or professional advice in any manner or form.

  4. #4
    Gold Member irneb's Avatar
    Join Date
    Apr 2007
    Location
    Jhb
    Posts
    625
    Thanks
    37
    Thanked 111 Times in 97 Posts
    Yep, Excel 2007's work like that. But I prefer 2010+: It allows the drop-down's source list to much more easily be read from a table as well (instead of just a fixed address). Works wonderfully for lookup tables!
    Gold is the money of kings; silver is the money of gentlemen; barter is the money of peasants; but debt is the money of slaves. - Norm Franz
    And central banks are the slave clearing houses

  5. #5
    Platinum Member sterne.law@gmail.com's Avatar
    Join Date
    Oct 2009
    Location
    Durban
    Posts
    1,328
    Thanks
    38
    Thanked 561 Times in 410 Posts
    Blog Entries
    7
    I'm finding pivot tables effective for data integrity management.
    Anthony Sterne

    www.acumenholdings.co.za
    DISCLAIMER The above is merely a comment in discussion form and an open public arena. It does not constitute a legal opinion or professional advice in any manner or form.

  6. #6
    Diamond Member adrianh's Avatar
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    5,089
    Thanks
    336
    Thanked 808 Times in 642 Posts
    This is the single most useful bit of code I have ever come across for Excel. The code allows you to write modules that are fired during cell entry. So, you are able to fire off lots of different events due to one selection being made in your dropdown list.

    This is my scenario:
    I do laser cutting and when I do costing I need to be able to select one material from a list of 10. The list of 10 materials may have different thicknesses.
    So
    Plywood
    1mm
    2mm
    3mm

    UHI
    1.2mm
    2.5mm
    3mm

    Now, each material / thickness has a related board size and price which gives me a calculated cost / cm sq.

    When I select a particular material I have to restrict the selection of thickness to only those specified for the material type and once I select the material thickness I have to populate the material length, width, price and calculated price / cm sq fields.

    I couldn't figure out how to do this because Vlookup etc is not clever enough to restrict the secondary selections.

    This code allows you to do anything you like once data has been entered into a cell.

    Run the example and you'll quickly figure out how useful the code could be.


    XL: How to Run a Macro When Certain Cells Change

    http://support.microsoft.com/kb/142154
    How easily someone is offended is directly proportional to how stupid they are.
    ~GS Elevator

  7. #7
    Platinum Member sterne.law@gmail.com's Avatar
    Join Date
    Oct 2009
    Location
    Durban
    Posts
    1,328
    Thanks
    38
    Thanked 561 Times in 410 Posts
    Blog Entries
    7
    Hello Adrian,

    I may have a simplier way to do the same

    D E F
    part type supplier

    plywood 2 45

    WOOD THICK COST
    plywood 1 35
    plywood 2 45
    plywood 3 55
    UHI 1.2 65
    UHI 2.5 75
    UHI 3 85


    The table is set
    Two items in bold are the selection criteria
    Coloured number Is the value returned by the following formulae - =INDEX(D7:F12,MATCH(D4&E4,D7:D12&E7:E12,0),3)
    Last edited by Dave A; 04-Oct-13 at 01:08 PM. Reason: tidy
    Anthony Sterne

    www.acumenholdings.co.za
    DISCLAIMER The above is merely a comment in discussion form and an open public arena. It does not constitute a legal opinion or professional advice in any manner or form.

  8. #8
    Platinum Member sterne.law@gmail.com's Avatar
    Join Date
    Oct 2009
    Location
    Durban
    Posts
    1,328
    Thanks
    38
    Thanked 561 Times in 410 Posts
    Blog Entries
    7
    The smiley face is actually a colon :

    so it reads d7:d12
    Anthony Sterne

    www.acumenholdings.co.za
    DISCLAIMER The above is merely a comment in discussion form and an open public arena. It does not constitute a legal opinion or professional advice in any manner or form.

  9. #9
    Site Caretaker Dave A's Avatar
    Join Date
    May 2006
    Location
    Durban, South Africa
    Posts
    20,978
    Thanks
    3,055
    Thanked 2,462 Times in 2,067 Posts
    Blog Entries
    12
    Quote Originally Posted by sterne.law@gmail.com View Post
    The smiley face is actually a colon :
    I took the liberty of wrapping the problem section with noparse BB tags for you. The unintended smiley is now history
    The trouble with opportunity is it normally comes dressed up as work.

Similar Threads

  1. How fast does the water level in your pool drop?
    By Pap_sak in forum General Chat Forum
    Replies: 6
    Last Post: 31-Oct-12, 04:22 PM
  2. Pastel Iron Tree v Drop Box
    By Imp in forum Accounting Forum
    Replies: 4
    Last Post: 08-Jun-12, 04:29 PM
  3. Help please - voltage drop
    By Gei in forum Electrical Contracting Industry Forum
    Replies: 3
    Last Post: 21-Jan-12, 09:26 AM
  4. Vodacom signal drop
    By twinscythe12332 in forum Technology Forum
    Replies: 3
    Last Post: 11-Oct-10, 06:31 AM
  5. Drop your price.
    By Dave A in forum Entrepreneurship and Business Management Forum
    Replies: 9
    Last Post: 10-Oct-09, 08:51 PM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

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