+ Reply to Thread
Results 1 to 9 of 9

Create Data validation list from matched criteria value results

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    13

    Create Data validation list from matched criteria value results

    Please see attached example workbook. I’m trying to figure out how to create a Data validation list that will look up two criteria values and populate a list with all results.

    In the example workbook on sheet “Main” I’d like the Data Validation lists in the yellow marked area, for example cell “B4” to look up the location value in cell “B1” and the product value in cell “A4”, match these two values (LO01 & BBT20) in sheet “Data” column “A” & “B” and return values of column “C” and use the results as the Data Valuation list in cell “B4” on sheet “Main”.

    In this example sheet “Main” cell “B4” would result in a Data Validation list of the following;

    DD0012.01
    DD0012.02
    DD0012.05

    Thanks in advance

    Luke
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-27-2009
    Location
    Surat,India
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Create Data validation list from matched criteria value results

    This will help you figure out solution.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    13

    Re: Create Data validation list from matched criteria value results

    Quote Originally Posted by hifliers View Post
    This will help you figure out solution.
    Hifliers thanks for your reply but this does not help me.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create Data validation list from matched criteria value results

    Hi Luke,

    The topic is called Cascading Validation Lists and Hifliers did point you in the right direction. Now you get to read about it at:

    http://www.excel-user.com/2011/02/ca...ion-lists.html

    I think this is what you are asking for, but if not keep asking and give us an example of what you expect to happen.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    13

    Re: Create Data validation list from matched criteria value results

    Quote Originally Posted by MarvinP View Post
    Hi Luke,

    The topic is called Cascading Validation Lists and Hifliers did point you in the right direction. Now you get to read about it at:

    http://www.excel-user.com/2011/02/ca...ion-lists.html

    I think this is what you are asking for, but if not keep asking and give us an example of what you expect to happen.
    Hi MarvinP

    Please see the image attached. Hopefully this helps explains what I'm after exactly.

    Cheers

    Luke
    Attached Images Attached Images

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create Data validation list from matched criteria value results

    Ok Luke,

    See the attached that has lots of fancy features. It starts with creating an Advanced Filter on the Data Sheet. I have formula in E2 on the Data sheet so it will filter whatever you have on your Main sheet in B1.
    Then there is an Event code behind the Main sheet, so when you click anywhere on the sheet it will fill in the Product name on the Data Sheet in F2 so the Advanced filter can fire.
    The Advanced Filter will reflect what is your Location and Product and do it's work and give what you want on the Data Sheet in Column K.

    Lastly I've created a Dynamic Named Range (DNR) called "BatchList" which I use as the Validation List.

    Lots of fancy and tricky stuff in here. Hope it works for you.... See the attached.. It should work for much bigger datasets.

  7. #7
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    13

    Re: Create Data validation list from matched criteria value results

    Quote Originally Posted by MarvinP View Post
    Ok Luke,

    See the attached that has lots of fancy features. It starts with creating an Advanced Filter on the Data Sheet. I have formula in E2 on the Data sheet so it will filter whatever you have on your Main sheet in B1.
    Then there is an Event code behind the Main sheet, so when you click anywhere on the sheet it will fill in the Product name on the Data Sheet in F2 so the Advanced filter can fire.
    The Advanced Filter will reflect what is your Location and Product and do it's work and give what you want on the Data Sheet in Column K.

    Lastly I've created a Dynamic Named Range (DNR) called "BatchList" which I use as the Validation List.

    Lots of fancy and tricky stuff in here. Hope it works for you.... See the attached.. It should work for much bigger datasets.
    MarvinP,

    Thanks for sharing this info, it took a bit for me to apply it to my official workbook and get everything running as intended and I'm very pleased with the results. However the Main sheet in the workbook has now lost its ability to copy/paste, maybe due to the event code..? Do you have an idea of how to fix this? Or can the event code be changed only to trigger off of the cells needing dropdown lists, so that the remainder of the sheet can still use Copy/Paste?

    Luke

  8. #8
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    13

    Re: Create Data validation list from matched criteria value results

    I add the following change to the code and everything is working perfectly. Thanks again for all your help!

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Column <> 4 Then Exit Sub
    Sheets("Data").Cells(2, "F") = Cells(Target.Row, "C")
    Call AdvFilter
    End Sub

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create Data validation list from matched criteria value results

    Hi luke,

    The real smart programmers do this kind of test...


    Please Login or Register  to view this content.
    end if

+ 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] List data from relevant cells if criteria matched in column
    By dvs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2014, 03:02 PM
  2. [SOLVED] Create Dynamic Data Validation List based upon Criteria
    By stubbsj in forum Excel General
    Replies: 6
    Last Post: 06-04-2012, 08:33 AM
  3. Replies: 5
    Last Post: 03-12-2010, 10:56 AM
  4. Trying to create complex list results, using validation lists, etc...
    By semantik in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-28-2007, 07:56 PM
  5. Replies: 2
    Last Post: 07-12-2006, 10:40 PM

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