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