•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
=FILTER for scheduling, spill workaround?
Our take
Are you struggling with the limitations of the =FILTER function for scheduling in your spreadsheet? You’re not alone. Many users face challenges when trying to dynamically pull data based on a single sales order number, especially when dealing with multiple entries. While the =FILTER function can effectively return related data, it often leads to frustrating #spill errors when applied across multiple rows. Let’s explore potential solutions and workarounds that maintain simplicity for users with varying technical abilities, ensuring everyone can benefit from a streamlined workflow.
Hey everyone, I feel as though I am doomed here in what I'm trying to achieve here but figured I'd give it a shot.
Above is the end goal where 'customer'/ 'product'/ 'order qty'/ 'ship date' is being populated based on just inputting a sales order number in column A. Each cell in the 'Customer' column is using =FILTER to spill out data into the subsequent columns by finding the sales order number from a separate master sales order sheet and pulling it from that specific column in the 'Sales Order's row.
Of course, above is the cruel #spill reality I am burdened with once I drag the formula cell down so this can be used dynamically. I've used XLOOKUP in the past which worked okay, but if a sales order number had multiple rows of items like "Bike"s order in the end goal picture, it only returns the first value.
=FILTER(SO!$H$2:$L$69,SO!$G$2:$G$69=B6,"") This is the formula I'm using currently, maybe I'm missing something or there's some complicated work around. This sheet is to be used by multiple people of varying technical ability so being very simple is imperative.
Anything is helpful or just let me know if this aint gonna work boys.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Generating a random selection of rows based on multiple criteriaI am trying to create a force building tool for a tabletop wargame. I would like it to generate multiple rows of unit info from a table based on certain criteria and have it working for the most part but keep ending up with #SPILL errors. I am sure that there is a much more efficient way of going about this. Here's the overview: https://preview.redd.it/4ghjhqiupksg1.png?width=1256&format=png&auto=webp&s=a6bdc0749397463685bc7f52d8e90f9199fb8d62 The breakdown: A random rom is selected from a table in the worksheet https://preview.redd.it/5telq4v4qksg1.png?width=757&format=png&auto=webp&s=f25aeec460a6b8e5c5750f9d3ff55acaef477658 Then a filter is applied to sort the results according to what is selected in the dropdown menu at B2 https://preview.redd.it/8mt7wxadqksg1.png?width=1236&format=png&auto=webp&s=1e2e6bc4cde488809447de96e1a8d38e8182e8f2 For the most part this method seems to work. The issue is that every few refreshes i get a #SPILL error https://preview.redd.it/ndkvd72nqksg1.png?width=257&format=png&auto=webp&s=57bbb4d9217c9166376c2935a0ce4f85c4b386c0 I have set this formula to run at the bottom of the sheet and ensured there was no data in any cells to the farthest right and down. BONUS: If there is also a way to get this to work and have the random rows be equal to a determined point value. that would be amazing. submitted by /u/simpson95338 [link] [comments]
- Sync or map data of two automated columns to the filtering systems of other columnsContext: The automated columns are C (Assigned Codes) and D (Positions). These were transferred from one workbook to another, which is this sheet you're seeing. I used the dynamic array filter function because it has to update in real time, as instructed by my manager. Example, my formula for column C: =FILTER([practicing.xlsx]Sheet1!B:B,[practicing.xlsx]Sheet1!B:B<>"",""") Thus, once the source workbook has more data, it can automatically show in this sheet. Reasons for not using alternatives: Power Query - it's not entirely automatic due to the load every x minutes, and the source workbook has to be closed for it to load in the destination workbook. Power Automate - blocked by my company The Problem: Column C and D aren't linked with the filtering systems of Column A (country) and Column B (Leader Assigned). For example, if the country USA is filtered/selected, then its assigned codes and positions should show. The issue is that their country code (starts with "US") and position, IT, are placed in different rows. If the USA is selected, it will only show rows C3-C4 & D3-D4, which is incorrect. https://preview.redd.it/ursej4pq8jxg1.png?width=916&format=png&auto=webp&s=272aa9d905e6d9e5277c70accc67e2614cc03dbd What I'm looking for: My assigned codes and positions already contain formulas (dynamic array filter function), so using another formula for these columns or in one cell can't be done (I suppose). Is there any way to map the C and D columns to the filtering systems for columns A and B? What I tried doing: Advanced filter - it adds a whole new table, but this sadly isn't what I'm looking for with my data. I want to just use the columns that I have now Custom filter - used the text filter -> begins with. It helps with filtering columns C and D for sure, but it doesn't remap the rows, so the data for columns A and B will appear inaccurate. Please let me know if I am also doing something wrong with what I've tried or done. Thank you in advance, and let me know if anything is unclear. This would really mean a lot to me. I am also open to chatting more! :)) submitted by /u/jeankrstein [link] [comments]
- How do I create dynamic dropdowns that works 2 ways?I have an Order Form sheet set up with a fair bit of code in the background. Part of the Form has order lines with headers of Code, Category and Product Description. I have a separate Products list which I wanted to create the dynamic drop down lists using formulas. I soon found out that you cannot type a FILTER formula directly into the Excel Data Validation source box. After a bit of researching, I figured how to to create dynamic dropdowns by referencing a spill range. The way I've set it up is probably a bit messy (but it works). At the moment the user has to select the category of product they want first. Then the drop down list in Product Description will show only items in that category. When they select the item they want, the drop down list for Code will show the specific code that item relates to. It can work but I was hoping to be able to use partial searches in any of the 3 columns to generate suggestions. eg. Start typing partial item name which would then narrow down options in the drop down list for Products as you type each letter. Select the item and the category and Item Code self populates. Also enable the user to partially type item code or category for a similar result. Hope this makes sense. And if so, any ideas? submitted by /u/AdComplete9707 [link] [comments]
- Formula for cascading filter optionsI'm building a quoting tool that has dynamic pricing based on options selected via drop downs. However, some (but not all) drop downs require cascading as the options available will be different depending on what's selected in an earlier drop down (in the same row). Here's the formula I'm using: =SORT(UNIQUE(FILTER(SizeKits[KIT],SizeKits[SIZE]='Other Sheet'!C13))) SIZE and KIT are named ranges. Depending on SIZE selected, it dictates the drop down options presented in KIT drop down. I've then got a lookup table referenced that has all of the options for KIT available depending on what SIZE is selected. Then I've got Data Validation to point to this formula on the "helper" sheet. This formula is on a "helper" sheet, where the quoting happens on 'Other Sheet'. The problem that I'm running into is that it's directly referencing cell C13 in Other Sheet for the other rows. There are seven rows of cells where users can select different product options, but I'm not sure how to make each row independent of each other. For example, C14 should have it's own options available depending on the other options selected in row 14. How do I make each row independent of each other? submitted by /u/Kermie88 [link] [comments]