Closed Thread
Results 1 to 18 of 18

Dependent Drop down list with DEPENDENT DEFAULT VALUE

  1. #1
    Registered User
    Join Date
    01-22-2010
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    5

    Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Hello, all
    It must be pretty simple to do it but I'm lost..

    Situation:
    I have two drop down lists 1) Country and 2) States/province

    Country has list: [US, Canada]

    If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
    If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list

    Scenario:
    First, I select "US" and choose the states to "Florida"
    Next I change the country to "Canada" and forgot to choose province
    Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
    BUT the current value is still "Florida"

    Probem:
    Now I have "Canada" and "Florida" selected in the sheet

    Question:
    If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
    Could someone help? I have a serious problem with this issue. Example EXCEL file is attached below

    Thanks in advance
    Attached Files Attached Files
    Last edited by ginieman; 01-22-2010 at 02:53 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Here, look at this link:

    http://www.contextures.com/xlDataVal02.html

  3. #3
    Registered User
    Join Date
    01-22-2010
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Hi zbor, I have read this link before but was not sure if there was a solution. Could you be more specific?

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Ah, I see you've already make it by dependent validation...

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    This can be accomplished with some short VBA code in the worksheet.
    Right click on the worksheet tab and paste this code into the code window
    Please Login or Register  to view this content.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    01-22-2010
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Quote Originally Posted by ChemistB View Post
    This can be accomplished with some short VBA code in the worksheet.
    Right click on the worksheet tab and paste this code into the code window
    Please Login or Register  to view this content.
    Does that work for you?
    Yes, it beautifully works. Thanks very much.
    However, is the VB the only way to approach this problem?
    Do you know any other solution without VB?

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Sorry, don't know of another way to do it without VBA.

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    You could create a formula in the cell first, before the Data Validation is applied.

    =LOOKUP(A2, {0,"Canada","US"},{"","Alberta","Florida"})

    Once that formula is in place, you could then apply the data validation to give you the drop down list in the same cell.

    However, the first time you use the drop down to select an option OTHER than that offered automatically by the formula...the formula is forever erased. The DV active in that cell would also probably inhibit your attempts to re-enter the formula.

    So this solution works fine for getting a "default" answer to appear in a row of data the first time. If you edit the choice to one of your own, you're making a permanent deletion of the formula on that row, any other rows further down your data still waiting for data would still have the formula in them, etc.

    So, if this is meant to be used in the same two cells over and over again, it won't work. VBA would be the only avenue for recycling this behavior in the same cells over and over again even if you've made other manual choices in the interim.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  9. #9
    Registered User
    Join Date
    01-22-2010
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Thank you very much,
    I should probably go for the VBA option because this check should be done over and over again.

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Then something like this:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-22-2010
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    This method seems to work fine too.
    Thanks a lot guys

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and add [SOLVED] to the start of your title. (unless the PREFIX box is still available, if so, set that to SOLVED.


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

  13. #13
    Registered User
    Join Date
    04-16-2013
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Quote Originally Posted by ChemistB View Post
    This can be accomplished with some short VBA code in the worksheet.
    Right click on the worksheet tab and paste this code into the code window
    Please Login or Register  to view this content.
    Does that work for you?
    I had a similar issue, but I have several dependent drop down lists in the same spreadsheet. Is there a way for me to apply this same code to a whole column rather than to 1 specific cell?

  14. #14
    Registered User
    Join Date
    06-08-2013
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Quote Originally Posted by ChemistB View Post
    This can be accomplished with some short VBA code in the worksheet.
    Right click on the worksheet tab and paste this code into the code window
    Please Login or Register  to view this content.
    Does that work for you?
    This is code is beautiful. It was a great help for me after a bit of customization.

  15. #15
    Registered User
    Join Date
    06-16-2010
    Location
    Bangalore,India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Hello ChemistB,

    This works beautifully for my requirement. But can you please inform how it can be extended to a range of cells in the same row, like in this case from A2 to A10
    Thanks in advance.

  16. #16
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  17. #17
    Registered User
    Join Date
    11-18-2012
    Location
    Pville,LA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Quote Originally Posted by MJBrad View Post
    I had a similar issue, but I have several dependent drop down lists in the same spreadsheet. Is there a way for me to apply this same code to a whole column rather than to 1 specific cell?
    i had the same question, basiclly when a drop list in B2 changed could Lists in D9 - D18 be changed to default value

  18. #18
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed 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