•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Modified PIVOTBY or dynamic array formula to lookup across rows and columns
Our take
Are legacy spreadsheet methods limiting your ability to analyze data effectively? Embracing dynamic array formulas, like a modified PIVOTBY, can transform your data presentation. With a single formula, you can seamlessly organize and visualize your project metrics across rows and columns, ensuring clarity and accessibility. Imagine effortlessly displaying targets and actuals for multiple projects over time, all in one dynamic view. Keep reading to discover how to implement this solution and elevate your data analysis to new heights.
Hi
I have the following source data:
| Week | Project | Area | Target | Actual |
|---|---|---|---|---|
| 4-Apr-25 | Proj 01 | North | 50 | 100 |
| 11-Apr-25 | Proj 01 | North | 150 | 120 |
| 18-Apr-25 | Proj 01 | North | 300 | 50 |
| 2-May-25 | Proj 01 | North | 500 | 70 |
| 4-Apr-25 | Proj 02 | South | 10 | 200 |
I need a single dynamic formula to spill across rows and columns to give this result :
| Project | Area | Measure | 4-Apr-25 | 11-Apr-25 | 18-Apr-25 | 2-May-25 |
|---|---|---|---|---|---|---|
| Proj 01 | North | Target | 50 | 100 | 300 | 500 |
| Proj 01 | North | Actual | 100 | 120 | 50 | 70 |
| Proj 02 | South | Target | 10 | - | - | - |
| Proj 02 | South | Actual | 200 | - | - | - |
I tried a PIVOTBY solution but couldn't quite achieve this result, or another option is to go for fixed values for the first three columns & dates and a spilling formula for the values
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Dynamic function to broadcast sum based on filtered criteriaHi I have the following source table: Product Region Month Target Actual Comp N Mar-26 100 50 Comp N Jan-26 60 120 PC S Feb-26 100 20 PC S Apr-26 10 200 Mobile W May-26 20 100 I have another table used for presentation with fixed rows and columns arranged this way : Region Col Jan-26 Feb-26 Mar-26 Apr-26 May-26 N Target S Actual I need a dynamic formula to spill across this grid. The rows and columns can increase so the solution should be scalable. Also there is another range with required list of products. for e.g range A1:A2 with items "Comp" and "PC", so the formula should be able to filter the sum based on items in this list. So the expected result is : Region Col Jan-26 Feb-26 Mar-26 Apr-26 May-26 N Target 60 0 100 0 0 S Actual 0 20 0 200 0 submitted by /u/land_cruizer [link] [comments]
- Dynamic YTD Formula from Pivot Table DataHi, I was tasked with creating a dynamic P&L statement from a trial balance query. The P&L statement is supposed to have a period selection tab(select period type(month, quarter) -> select period(june, q2, etc). I have to come up with a formula that calculates CY YTD and PY YTD and automatically updates whenever the selected period is changed. Does anyone have any idea as to how I can do this? And if there's any way to achieve this without the Pivot Table, thats also fine. I've been stuck for hours. https://preview.redd.it/qk83w0iifpxg1.png?width=1716&format=png&auto=webp&s=3e6d8be78665054293ecff77a0e659a6acbc254a https://preview.redd.it/dfa9x2iifpxg1.png?width=1378&format=png&auto=webp&s=ea6996e5648327ad13c9bb3da9339137da80e825 submitted by /u/Relative-Neck6922 [link] [comments]
- Summarise 2D Dynamic Array?Using Excel 365 on Windows 11 This should be simple, but I haven’t manged to find a way to do it (and Copilot has just generated a bunch of #REF! and #CALC! errors…) I’m trying to do some basic forecasting over multiple years, but using dynamic ranges so the start and end dates, and the number of categories forecast can update automatically. I can get to a dynamic forecast by month, but am drawing a blank when I try to summarise that by year. A simplified version of the sheet currently looks like this: https://preview.redd.it/li08zsp8w7rg1.png?width=584&format=png&auto=webp&s=2d61ac0c1d887bb3b18bb3f08a9542fbc72f439b Data entry in B4:E10 Months dynamic range (pink) calculated in C14 =EOMONTH(D2,SEQUENCE(1,F2,0,1)) and spills C14 to AL14, or wherever the last month is. Categories dynamic range (pale blue) calculated in B15 =FILTER(B4:.B10,B4:.B10<>"")) and spills B15 to B20 here. Years dynamic range (peach) is just a helper row =YEAR(C14#). It might not even be necessary other than visually. The Forecast dynamic range (green) is then =XLOOKUP($B15#,$B$4:.$B$10,$C$4:.$C$10,0)*(C14#>=XLOOKUP($B15#,$B$4:.$B$10,$D$4:.$D$10,0))*(C14#<=XLOOKUP($B15#,$B$4:.$B$10,$E$4:.$E$10,0)) All I want to do is put one formula in C25 to calculate the blue dynamic range total by year for each category. The years are a dynamic range (UNIQUE of C14 above) and the categories are just B15#. This it turns out is completely beyond me - I can calculate each row individually using SUMIFS quite easily, but cannot persuade it to calculate one SUMIF for each line using one formula Anyone got a good way to deal with this? Thanks. submitted by /u/sprainedmind [link] [comments]