Or if you have multiple queries, just refer to yours by name: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range('MyArea')) Is Nothing Then ThisWorkbook.RefreshAll I get my query to refresh automatically when the criteria is changed with this code, added to the worksheets code (Right click the worksheet name and 'view code'): Private Sub Worksheet_Change(ByVal Target As Range) You will have to remember to click 'refresh' when the criteria is changed to tell PowerQuery to look the data up again. Load this table to your workbook and cut/paste to move it to the right spot. That's it! Just like the other examples, you could filter other columns for other criteria, then just add more variables/named ranges to this code. #'Filtered Rows' = Table.SelectRows(#'Changed Type', each ( = AreaCriteria)) We need a trick to get not just the first match, but all the matches - so we use a hacky technique called an Nth-Matching. I'm going to assume here know how VLOOKUP and INDEX/MATCH work - so you know you could use INDEX/MATCH in this scenario to find the first occurrence of North, and pull back the data: =INDEX(ItemCol,Match(Area,AreaCol,0) Better yet, you should format your data as a Table (Ctrl+T) and then you can reference columns by their name automatically, like DataTable instead of A2:A23. Naming your ranges can make these formulas much easier to write. I've named the green Area lookup cell MyArea. These solutions are meant to work without needing to write your own VBA code.īefore diving in, I want to point out that Excel 365 will at some point be introducing the FILTER() function which will make this extremely easy - you can make a dynamic filtered list with a single short formula in one cell like this! Until then, we're stuck with the methods below! Feel free to comment if you have another, perhaps easier method.Īlso note - in this setup I've named the first three columns ItemCol, AreaCol, and PriceCol. There are about a hundred ways to do this with some custom macros so I'm not going to cover those.

Cons: Requires Excel 2013+ on PC, does not refresh automatically, slower to set up.

Cons: Requires adding VBA code to your spreadsheet.

Cons: Array formula, can be slow if used in bulk.

Pros: No code or special features needed.

I'll cover what I think are the three best options for getting this done: This sounds like something that should be simple in Excel, but it's actually a bit of a pain. Your first thoughts should be to add a filter or slicer to the table and just filter the list in place but for this post, let's assume you need to get those matching items to some other spot in the spreadsheet. So you have a table of data, and you want to be able to dynamically show all the items that meet some criteria: in this case, all the items from the North area.

How to create a dynamic filtered list - three methods Recent ClippyPoint Milestones !Ĭongratulations and thank you to these contributors DateĪ community since MaDownload the official /r/Excel Add-in to convert Excel cells into a table that can be posted using reddit's markdown. Include a screenshot, use the tableit website, or use the ExcelToReddit converter (courtesy of u/tirlibibi17) to present your data. NOTE: For VBA, you can select code in your VBA window, press Tab, then copy and paste that into your post or comment. To keep Reddit from mangling your formulas and other code, display it using inline-code or put it in a code-block This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified Only text posts are accepted you can have images in Text posts.Use the appropriate flair for non-questions.Post titles must be specific to your problem.

Excel for mac auto enter list items