•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to return value from one row above when filtered? Building dynamic filtered pareto
Our take
In this guide, we’ll explore how to return a value from one row above when applying filters in a dynamic 80/20 Pareto chart. Specifically, you'll learn to create a balance sheet formula in column B that references the cell above while allowing for filtering by sales representatives in column E. This technique ensures that your formulas adapt to different filtered views, maintaining the integrity of your data analysis. Join us as we simplify this process and empower your spreadsheet skills for better productivity.
Is there a way to return the value from one row above when filtered? To add context, I am trying to build an 80/20 Pareto chart utilizing a sort of balance sheet formula in column B where cell B5, as the first row of data, returns a copy of the cell contents in C5. However, I need to be able to filter to different sales reps in col E while maintaining the unique formulas in B5 vs B6. In other words, the first row of data changes when the filter on column E is activated.
| Col A | Col B | Col C | Col D | Col E | Col F sorted desc |
|---|---|---|---|---|---|
| Row 1 | 80/20 Pareto | Subtotal: | 100 | ||
| Row 4 | Customer | Sales Rep | Value | ||
| Row 5 | =C5 | =25/100 | XYZ Customer | Sales Rep A | 75 |
| Row 6 | =B5+C6 | =75/100 | 123 Customer | Sales Rep A | 25 |
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]