**Our Take: Transform your data with a single formula that spans rows and columns**
This is exactly the kind of problem that shows why traditional spreadsheets are holding you back. A user on Reddit has clean source data, weeks, projects, areas, targets, and actuals, and wants a single dynamic formula that spills across rows and columns to produce a clean pivot. They tried PIVOTBY and couldn't make it work. That's not a failure of effort; it's a failure of the tool. The fact that someone has to ask for a workaround, fixed values for the first three columns plus a spilling formula for the values, tells you everything about the gap between what modern data work demands and what legacy spreadsheets deliver.
Here's what this means for you in practical terms. You're likely facing the same friction: you have data that repeats across rows, you need to reorganize it by project and area over time, and you want the result to update automatically when new weeks appear. The Reddit user's source data is simple, five columns, four weeks, two projects, but the transformation is anything but simple in a traditional grid. A PIVOTBY approach should work conceptually, but the formula syntax and the need to handle missing dates (like the dashes for Proj 02 in later weeks) reveal the limits. You end up spending more time wrestling with the formula than you do understanding the data.
We think the real insight here is not about a specific formula hack. It's about recognizing when a tool is asking you to compensate for its weaknesses. The user's proposed solution, fixed columns for Project, Area, and Measure, then a spilling formula for the date columns, is a pragmatic workaround, but it's a workaround nonetheless. In a better system, you'd write one expression that says "take this table, unpivot the Target and Actual columns, then pivot the weeks into columns." That's it. No manual column setup. No worrying about whether PIVOTBY handles the layout you want.
The practical takeaway is straightforward: if you find yourself designing workarounds for basic pivots, it's time to ask whether your spreadsheet is the right tool for the job. The user's data is small, but the pattern scales. Every time you add a project, a week, or a measure, the manual scaffolding around the formula grows. A single formula that spans rows and columns should be the default, not the exception. Stop patching the gaps. Start using tools that treat data transformation as a first-class operation, not a puzzle to solve.