View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.misc
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Automagic Subtotals in Pivot Table

On 5/6/2021 1:48 PM, Claus Busch wrote:
Hi again,

Thanks, Claus. It doesn't seem to work with Excel 2016...whenever try
to Right click and subtotal, get error of

"A PivotTable report cannot overlay another PivotTable report"


have a look:
https://www.myexcelonline.com/blog/a...port-solution/


OK, thanks Claus. I hadn't found the thingie about "Show All Subtotals
at Bottom|Top of Group" before. Turns out it was apparently trying to
use "Bottom" instead of "Top" which adds another line in every group and
that caused the PivotTable to grow that I wasn't expecting/didn't see
any reason why it should. Of course, it doesn't tell you that... :(

Anyways, trying to add all those extra lines did cause the overall
length grow into an area where I thought it possible for it to go and
that did have my other calculated table in it. So, the mystery is
explained.

It's a pretty useful feature, but surely could stand some improved
diagnostics and interface tools.

Looks like in the end it will be workable if I can figure out how to
autogenerate it with the code that populates the rest of the sheet.

It has anywhere from 4-8 separate sections corresponding to monthly
scholarship billings that are extracted from the workbook Financial Aid
sends as the bill to the Foundation; we have to then code the funds to
which individual awards are to be billed internally to/tracked by the
Foundation by the source of the funds.

I create that workbook by reading the required columns and building a
new workbook for the accountant/bookkeeper; all these individual sums
had been being calculated by hand by sorting the workbook on the ACCOUNT
and writing a SUM() manually.

I need now to be able to insert the given PivotTable for the monthly
billing sections when create the table -- of course it's possible to do
it by hand, but shouldn't be necessary to do so.

Thanks again for the pointers; this is my first foray into PTs in depth,
and I'm no Excel expert to begin with...

--dpb