+ Reply to Thread
Results 1 to 7 of 7

Data validation list - Formula to look for more than 1 criteria within the same cell

  1. #1
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Data validation list - Formula to look for more than 1 criteria within the same cell

    Hello,

    I am trying to modify an existing 2010 Excel file that is used by our organization. The original file had some basic dependent validation list depending on the choices made by the users filling out the form. For example, if the user chooses a region in the 1st field, then the 2nd field would only show the cities from that region.

    I was looking to modify the file in order for the user to be able to choose more than 1 region in the first field. I was able to find a VBA code to add to the worksheet to allow this. I also changed my validation list to include an array formula as it would simplify our file for the future and I suspect it would help resolve my problem.

    My question/problem is the following: If the user chooses more than 1 region in the 1st field, than the dependant validation list in the 2nd field does not work anymore. I understand that this no longer work because there is now more than 1 choices that was made in the 1st field. How you I modify the data validation formula in the 2nd field to allow to check for more than 1 criteria. For example, if the user chooses 2 regions in the first field, than the cities of those 2 regions should be shown to the user to select in the 2nd field.

    I have attached a sample file to illustrate my problem. Choices made by the user are in column D and E.

    Thank you for your time.
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Data validation list - Formula to look for more than 1 criteria within the same cell

    See if this is something you can work with.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Data validation list - Formula to look for more than 1 criteria within the same cell

    Hello sktneer,

    Thank you for your time and work, greatly appreciated! It does work as intended so I will mark as solved.

    However, my actual data does include several dependant validation lists across multiples column so I will have to play around with your code to see if I can make it work with my actual data. I'll let you know if I cannot come up with a solution on my own but this will likely take place in the new year If it is not too much trouble, can you quickly guide me on how I would use your macro if I had a 3rd validation list to use by having a 3rd field presented to the user that would consist of a list of offices that would be based on the 2nd field of cities (where the user could select multiples cities as well).

    I have included another file adding the 3rd field of offices. Thank you!
    Attached Files Attached Files
    Last edited by Chrispelletier; 12-24-2015 at 08:50 AM.

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Thumbs up Re: Data validation list - Formula to look for more than 1 criteria within the same cell

    Glad to know that the solution worked as per your requirement.
    Please find the attached workbook with the tweaked code so now the code will create a drop-down list for the Office column dependent on what is selected in the City drop-down.

    Hope this will resolve your issue.

    Wish You A Merry Christmas & Happy New Year Chrispelletier!
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Data validation list - Formula to look for more than 1 criteria within the same cell

    Hello Sktneer,

    Thank you so much for your quick response and additional work. Working as planned. With this addition I believe I will be able to modify it with my real data. Thank you again and Merry Christmas & Happy New Year to you and your family.

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Data validation list - Formula to look for more than 1 criteria within the same cell

    You're welcome. Glad I could help.

  7. #7
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Data validation list - Formula to look for more than 1 criteria within the same cell

    Quote Originally Posted by sktneer View Post
    You're welcome. Glad I could help.
    Hello Sktneer,

    Sorry to bother you again.

    I thought that with your help I would be able to figure out (i usually can) how to apply it to my own complete data but I have been trying for nearly 2 days with not much progress I understand you do this voluntarily so I totally understand if you cannot help me further but here is an attached file with my data re-arrange to better show my actual data/format and what I am looking for:

    • The user select data from validation fields on sheet User_Input in cell D2 (region), B5-7 (City), C5-7 (Office), D5-7 (product) and E5-7 (name)
    • My database for data validation is on sheet Validation_En
    • The user should be able to select more than 1 field from the validation list (I have included the VBA code on the User_input sheet so this is already covered)
    • When the user select the 1st field (region), the choice(s) made for the region will determine which choice will be available for the 2nd choice (city). You created a macro for me last time which I have included but may not match exactly since I modified the layout a little bit.
    • Even though the database list specific fields within a column more than once (ex: central region is listed 4 time), it should only should be shown as 1 choice to the user.
    • I have included 2 examples of what the final result could be under the sheet Final result.

    I have removed some elements from my original posting such as named range, array formula as you seem not to need them with the macro you created and honestly there are quite complicated to maintain and update and I though you may have something more easy to maintain. (I did leave the name range in there for you just in case).

    Again, thanks for your help and no worries if you cannot help me further. Thank you!
    Attached Files Attached Files

+ 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. [SOLVED] using a data validation drop down list AND an IF formula in the same cell
    By bite_tony in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-23-2021, 02:59 AM
  2. Replies: 8
    Last Post: 06-25-2014, 01:08 PM
  3. Formula to show an adjacent cell to the one picked in a data validation list
    By sengatwork in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2013, 02:30 PM
  4. Data Validation List and Custom formula in 1 cell
    By murugavelmsc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-11-2013, 02:50 AM
  5. Formula to add cell to named list / range (data validation)
    By neo5000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 10:57 AM
  6. Replies: 1
    Last Post: 09-05-2012, 11:39 AM
  7. Replies: 2
    Last Post: 07-12-2006, 10:40 PM

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