+ Reply to Thread
Results 1 to 8 of 8

Extract a List of Values Filtered by Criteria

  1. #1
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Extract a List of Values Filtered by Criteria

    Hello everyone,

    I'm working on a way to filter a running table I have using criteria that is dynamic. I had originally hoped to use VBA for this, but to no avail.

    I attached an example of what I am hoping to accomplish; on Sheet1, whatever combination of cells G2 and H2 are, I would like the filtered results from Sheet2 to be compiled within columns A:D on Sheet1. I know I could just use AutoFilter on the actual table within Sheet2, but the end goal is to have this as a front-end worksheet for the user, so minimum interaction with the database is preferred.

    Hope I was clear, and thanks to everyone in advance for any help.
    Attached Files Attached Files
    Last edited by mcmahobt; 12-15-2014 at 04:20 PM.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Extract a List of Values Filtered by Criteria

    Put this in the worksheet module for Sheet1:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: Extract a List of Values Filtered by Criteria

    On sheet 1 the options for Refrigerate and Rotten are YES and NO, but on sheet 2 the data show TRUE and FALSE.

    Which do you wish to change?

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Extract a List of Values Filtered by Criteria

    FlameRetired,

    I appreciate the catch! I updated the original example to reflect what I actually meant.

    Olly,

    I appreciate the coding help. I entered the code into the worksheet module for Sheet 1, but unfortunately it doesn't seem to be working on my end. There are no results on Sheet1 when G2 and H2 change, and Sheet2 seems to clear it's filter to show no results.

    Thank you both for the feedback.

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Extract a List of Values Filtered by Criteria

    My code translates YES / NO into TRUE / FALSE, to work with the workbook you uploaded... so if you've already changed those values somewhere, that will cause a problem.

    It's written to debug error descriptions to the immediates window - is there an error being returned after you change G2 or H2?

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Extract a List of Values Filtered by Criteria

    In case it helps, here's your original file with my code added, and tested working on my pc:mcmahobt.xlsm

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: Extract a List of Values Filtered by Criteria

    I'm not sure I have the concept, but here's what I came up with.

  8. #8
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Extract a List of Values Filtered by Criteria

    Olly, I appreciate the quick and thorough help! I really need to just bite the bullet and get into some books on VBA...

    FlameRetired, thanks for the effort. Although close to what I am looking for, my original hope was to pull something together within VBA and Olly's method should hopefully do the trick.

+ 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. Replies: 1
    Last Post: 11-09-2014, 06:04 PM
  2. Replies: 4
    Last Post: 01-18-2013, 05:10 AM
  3. [SOLVED] Extract unique values out of list based on an extra criteria
    By sven1975 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 05:02 AM
  4. add values in a filtered list
    By Casper05 in forum Excel General
    Replies: 2
    Last Post: 07-11-2008, 02:31 PM
  5. Counting the Values that Match Criteria in a Filtered List
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-28-2005, 10:38 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