Write to a Pivot Table?

[ghdunn]

This is an FAQ over the years, but I just wondered if recent developments (Power Pivot, 2016 etc) had taken it on at all.

I am looking to 'write back' to underlying data from a Pivot Table. I recognise that at anything more than a superficial level this is difficult as it would need to reverse engineer the functions that generate totals. However at its simplest implementation a pivot table simply reports a single row in an underlying table...is there any read/write capability that would allow that row to be altered.

I have particularly been intrigued by the concept of a 'columnar table' in memory...can this be written to.

Any details of add ins or links to simple macros would be appreciated.

 

[Matt Allington]

There is a third party product that will do this. It is very good, but you will need $1,500 + maintenance and Power Pivot knowledge. I works on a column data store in SSAS Tabular. Power Planner Software -

If you want a demo, I can facilitate that for you.

 

[ghdunn]

Matt - thanks for the reply and thanks for the offer. I was looking to do this with minimum extra overhead both in terms of cost and 'infrastructure' (I work for a large bank so getting anything in through the door is a faff). On a spectrum of 'inside the box' - 'simple add in' - 'vba code' - 'bigger implementation' I guess what you have in mind is closer to the right hand side. I am interested but just in the interests of time (yours as much as mine) if you don't mind I will continue my search for a while longer. I am surprised that this use case has not received more attention given the ubiquitous nature of pivot tables. I refer to FAQ in my original post but in fact google and this site do not throw up extensive treatments.

Anyone else any ideas.

 

[Matt Allington]

Auto, I understand. What you are asking here (write back to a pivot table) is not a trivial task. Pivot tables don't have the ability to write back to data natively, so changing that in Excel would be massive. enterprise software that has similar capabilities starts in the hundreds of thousands of dollars. Of course if you do find something, I would love to hear about it.