+ Reply to Thread
Results 1 to 11 of 11

Initial default drop downs and dependent default drop downs

  1. #1
    Registered User
    Join Date
    11-08-2016
    Location
    South Florida
    MS-Off Ver
    2016
    Posts
    6

    Initial default drop downs and dependent default drop downs

    I have 5 drop down lists in the B column. I can currently choose a food type(b1) and the respective food type lists will populate the remaining drop downs(b3-b6). On the data tab I have the named ranges and all drop down lists are populated through data validation.

    What I would like to do is select a food type and the remaining drop downs would show a default selection. I put item numbers in the A3-A6 labels to show that whatever food type is selected, I would like the food items to show in that order based on the list that is on the data tab (i.e. If "fruits" is selected then the drop downs would show banana, then apple, pear, and orange. If "vegetables" they would show broccoli, carrot, onion, then corn). Every time the food type is changed, the rest of the drop down defaults are populated with the correct defaults, however, if someone is to change the item drop down lists after the initial defaults, they would be able to do without issue.

    I understand this is probably a VBA thing and I am quite new to it. I know there is a way to do it by case and type in the string literals, but I would prefer pulling by the position in the list as I have many dependent drop down lists.

    Also, I would like to set an initial default for when the workbook is pulled up, so it can show an example of the fully filled out form.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Initial default drop downs and dependent default drop downs

    Firstly, change the source for your data validation drop-down list to this:

    =data!$B$1:$E$1

    Then type this into A3 of the drop-downs sheet and drag copy down:

    =IFERROR(INDEX(data!$B$2:$E$5,ROWS($1:1),MATCH($B$1,data!$B$1:$E$1)),"")

    You need to set the workbook to auto calculation (it is currently on manual).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Initial default drop downs and dependent default drop downs

    Right Click on your sheet name at the bottom of excel and select view code.

    Paste this code in the window that opens and close the window.

    Your data validation for types needs amending too


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 12-02-2016 at 01:02 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Registered User
    Join Date
    11-08-2016
    Location
    South Florida
    MS-Off Ver
    2016
    Posts
    6

    Re: Initial default drop downs and dependent default drop downs

    Hi AliGW,

    Unfortunately, this did not work for me. I got banana for A3-A6 and that is all. Assuming the A3 was a typo, I tried putting them in B3-B6 and got banana for all with no drop down lists.

  5. #5
    Registered User
    Join Date
    11-08-2016
    Location
    South Florida
    MS-Off Ver
    2016
    Posts
    6

    Re: Initial default drop downs and dependent default drop downs

    Quote Originally Posted by mehmetcik View Post
    Right Click on your sheet name at the bottom of excel and select view code.

    Paste this code in the window that opens and close the window.

    Your data validation for types needs amending too


    Please Login or Register  to view this content.
    Thank you for your post! This did work to a degree. However, I would like to put them in the order the labels specify.

    Where fruits data tab order is:

    Fruits

    banana
    orange
    apple
    pear

    I would like to know how to call them out of order. Item 1 would be banana, but the second label says Item 3 so the resulting default should be apple, and the third label says Item 4 so that would be pear, etc. Is there a way to do that?

    Also, what if I decide to put in spaces for the food types(i.e "Juicy Fruits," "Wholesome Vegetables," "Hearty Meats") is this going throw a wrench in the whole thing?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Initial default drop downs and dependent default drop downs

    Quote Originally Posted by hudsone777 View Post
    Hi AliGW,

    Unfortunately, this did not work for me. I got banana for A3-A6 and that is all. Assuming the A3 was a typo, I tried putting them in B3-B6 and got banana for all with no drop down lists.
    That's why I told you to turn auto-calculation on. It is currently set to manual. As soon as you set it to on, you will get the right results.

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Initial default drop downs and dependent default drop downs

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-08-2016
    Location
    South Florida
    MS-Off Ver
    2016
    Posts
    6

    Re: Initial default drop downs and dependent default drop downs

    Quote Originally Posted by AliGW View Post
    That's why I told you to turn auto-calculation on. It is currently set to manual. As soon as you set it to on, you will get the right results.
    Thank you for clarifying! I put it on auto and it worked! I did run into the same problem as mehmetcik's first solution.

    I would like to put them in the order the labels specify.

    Where fruits data tab order is:

    Fruits

    banana
    orange
    apple
    pear

    I would like to know how to call them out of order. Item 1 would be banana, but the second label says Item 3 so the resulting default should be apple, and the third label says Item 4 so that would be pear, etc. Is there a way to do that?

  9. #9
    Registered User
    Join Date
    11-08-2016
    Location
    South Florida
    MS-Off Ver
    2016
    Posts
    6

    Re: Initial default drop downs and dependent default drop downs

    Quote Originally Posted by hudsone777 View Post
    Thank you for clarifying! I put it on auto and it worked! I did run into the same problem as mehmetcik's first solution.

    I would like to put them in the order the labels specify.

    Where fruits data tab order is:

    Fruits

    banana
    orange
    apple
    pear

    I would like to know how to call them out of order. Item 1 would be banana, but the second label says Item 3 so the resulting default should be apple, and the third label says Item 4 so that would be pear, etc. Is there a way to do that?
    It appears I didn't test far enough. Meats returns the fruit list and Dairy shows nothing.

  10. #10
    Registered User
    Join Date
    11-08-2016
    Location
    South Florida
    MS-Off Ver
    2016
    Posts
    6

    Re: Initial default drop downs and dependent default drop downs

    Quote Originally Posted by mehmetcik View Post
    Right Click on your sheet name at the bottom of excel and select view code.

    Paste this code in the window that opens and close the window.

    Your data validation for types needs amending too


    Please Login or Register  to view this content.
    Hi there!

    Thank you for being so helpful! I have a workbook I am working on and it's a doozy.

    I just noticed this doesn't show a selectable drop down list for the items if you make a type that has spaces in it. I renamed fruits to juicy fruits. I tried putting in [code] =INDIRECT(SUBSTITUTE(B1," ",""))[code] for the validation source and it validated to an error and still didn't work.

    Forget the calling the items out of order for a second. If I were to use this code and just put the data in the order I would like it to appear, add more items, and want a blank row between the next items on the Drop down sheet, what would I change to do that? I've added the data and tried to add the B8:B9 and B11 ranges [CODE] Range("B3:B6,B8:B9,B11").Value [CODE], but, as you can see, that didn't work.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Initial default drop downs and dependent default drop downs

    Try this approach:-

    Please Login or Register  to view this content.

+ 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. Dependent Drop Downs
    By Bumblbree in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2016, 11:45 AM
  2. [SOLVED] Dependent drop downs
    By gameplayer in forum Excel General
    Replies: 2
    Last Post: 12-04-2015, 11:49 AM
  3. [SOLVED] Dependent Drop-Downs (Indexed)
    By BobBing in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2012, 05:33 PM
  4. Creating dependent drop downs
    By harrybelly in forum Excel General
    Replies: 2
    Last Post: 12-08-2011, 05:08 PM
  5. Dependent Drop-Downs
    By braydon16 in forum Excel General
    Replies: 2
    Last Post: 12-08-2011, 05:06 PM
  6. Auto-update initial drop-down selection based on previous drop-downs
    By thornomad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 09:55 AM
  7. Dependent drop downs.
    By Mattypb in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-16-2009, 08:47 AM

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