+ Reply to Thread
Results 1 to 21 of 21

Creating a dynamic dropdown list of a selection of a master list

  1. #1
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Creating a dynamic dropdown list of a selection of a master list

    Hi Everyone,

    I'm trying to create a drop down list from a master list including only items I've selected via checkbox.

    Attachment 395069

    My first thought was to use the string =IF(B2=True,A2,"") in column C to create the list then ignore blanks in the data validation list settings. I've since worked out that this won't work, but I haven't been able to work out a simple way to do what I need.

    Any help would be greatly appreciated.
    Thanks all

    Roo

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Creating a dynamic dropdown list of a selection of a master list

    Hi Welcome to the Forum,

    Could u please provide sample workbook it help us to give u proper answer
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  3. #3
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    Thank you,

    Sample workbook.xlsm

  4. #4
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Creating a dynamic dropdown list of a selection of a master list

    I have seen your file, but I unable to understand what exactly you want in output.

    could u plz explain me what exactly you want in output...

  5. #5
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    Column A in the sheet called Data is a master list of departments, Column B is a true/False that I want to either include or exclude the corresponding A cell in a drop down list.

    IE; In the screenshot attached to my first post the drop down list should include production and video.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Creating a dynamic dropdown list of a selection of a master list

    Hi there. I think that this is what you need....
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    Perfect, thank you very much!

  8. #8
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Creating a dynamic dropdown list of a selection of a master list

    plz try the attached file and let me know is it what your looking for..
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    Hi Glenn,

    I've just noticed an error and I can't see why it would occur...

    If I change production to false I still get it listed in the active list and the next item is removed.

    Any ideas? Thank you very much for your help.

    Roo

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Creating a dynamic dropdown list of a selection of a master list

    ?? I don't see that (or at least I don't think I do). can you re-post the attachment, where you have done as described?

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Creating a dynamic dropdown list of a selection of a master list

    Do you mean that if (for example) Video was selected in SIGN OUT, and then you change video from True to False, that video remains in sign out, until you update the dropdown? Or do you mean something else?

  12. #12
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    Thats just when I opened the sample workbook you sent back, It looks like an error (I initially figured graphical but it translates to the dropdown list) somewhere, I'll open it up on another machine and see if it still happens.

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Creating a dynamic dropdown list of a selection of a master list

    did you accidentally take the CSE {} brackets off the first row?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  14. #14
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    The attached screen shot shows production as false but it still appears in the active list

    excel screenshot 2.png

  15. #15
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Creating a dynamic dropdown list of a selection of a master list

    Hi Roomar

    have u seen my response, plz have a look this
    Attached Files Attached Files

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Creating a dynamic dropdown list of a selection of a master list

    Yep- Try Humdingaling's suggestion - I forgot to mention that I was using an array formula. If you accidentally un-set the array, that'd explain it.

    Array Formulas are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...

  17. #17
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Creating a dynamic dropdown list of a selection of a master list

    Naveed your solution is pretty similar to glenn's but i think they both can be simplified even more

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    you dont need to chuck the row back and forth as all you want to return from the k in small is 1 to 14

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Creating a dynamic dropdown list of a selection of a master list

    LoL - you're right!!! Another blonde moment...

  19. #19
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    Thanks again Glenn, Its back to working a treat!

    Roo

  20. #20
    Registered User
    Join Date
    05-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a dynamic dropdown list of a selection of a master list

    BTW, (because I'm a dumbarse and can't work this out either...) A very quick one for you...

    On the radio signout sheet, in the radio number column, I have tried to put a basic 'add new line' string (=IF(A3,B3+1,"")).

    It works perfectly if I put a number in cell A3, but if A3 contains any letters I get #VALUE.

    Why is this?

  21. #21
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Creating a dynamic dropdown list of a selection of a master list

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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. Creating a simple macro with selection from dropdown list
    By Tescatlipoca in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2014, 11:54 AM
  2. Make same selection for multiple drop down list and clear all for all dropdown list
    By salomip13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2014, 01:34 PM
  3. Replies: 9
    Last Post: 02-26-2014, 04:17 PM
  4. [SOLVED] VBA for creating dropdown list from dynamic multiple values lookup
    By costin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-29-2013, 05:39 PM
  5. Dynamic dropdown list selection based on combo box
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-10-2012, 10:55 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