Pivot App - Edit Data in Pivot

Tara Messa

It appears that you can edit the values in Pivot App but those values do not sync back to the source sheet, is that correct? We are looking for users to enter monthly projections in a pivot view because it is much more user friendly for input (and similar to their current Excel process), but we want the data to then push back to a sheet that we can use as our master data source for reporting.

Brian_Richardson

That's right, the Pivot is one-way from source to pivoted data sheet.

You might be able, though, to present your users with a "pivot-like" edit using grouped reports where you group by month and then cost code. Or cost code and then month. Still not as good as broken months into columns, I'll admit.

You could also try reversing your process. Have the data entry sheet with monthly columns, then (if needed) Unpivot those results. Unpivot is somewhat advanced to setup but works well once you have it going. See my post on how to do that:

https://community.smartsheet.com/discussion/98688/challenge-unpivot-financial-data-reverse-pivot

Tara Messa

Thanks and well done figuring out the unpivot. The solution doesn't quite work for my needs because I am looking to combine it with Data Shuttle or Data Mesh to bring in actuals, but it is good to know this is possible in the future.

Brian_Richardson

Yeah it’s a pain having to flip between layouts. I’m doing similar for our budget and Actuals. I ended up providing a data entry budget sheet in monthly column format, with budget columns and Actuals columns. Then the Actuals are brought in with a vertical format each month using Data Shuttle and “keyed” to match to budget line items. Then I have formulas in the Actuals monthly columns on the budget sheets that do sumifs formulas to bring the Actuals in by key and date into the correct Actuals month column.