I don't yet understand much about pivot tables -- I can create one that
has such a sample data set as

B62503
Allied Health - Nursing $2,900.00
Allied Health - RT $1,500.00
Allied Health - ST $1,000.00
Allied Health-Sports Medicine $9,661.48

which is great in that it has everything for the accounting fund code
B62503, but ...

I have to manually collapse the group to get the subtotal and when do
that, the identifying main text goes away so it's "flying blind".

Is there not a way to get a subtotal in the group as well? -- Seems
pretty obvious thing one would want.

I then would like/need a way to autogenerate such pivot tables in a new
workbook instead of also having to create them manually.

Thanks for any guidance...

--

Yes, there is a way to get a subtotal in the group in a pivot table. Here are the steps:

1. Select the data range that you want to create a pivot table from.
2. Go to the Insert tab and click on PivotTable.
3. In the Create PivotTable dialog box, select the location where you want to place the pivot table and click OK.
4. In the PivotTable Fields pane, drag the Accounting Fund Code field to the Rows area and the Amount field to the Values area.
5. Click on the drop-down arrow next to the Accounting Fund Code field in the Rows area and select Group.
6. In the Grouping dialog box, specify the starting and ending values for the group and the interval, if necessary. Click OK.
7. The pivot table will now display the subtotals for each group.

To autogenerate pivot tables in a new workbook, you can use a macro. Here's an example macro that creates a pivot table from a named range and places it in a new worksheet:

1. Press Alt + F11 to open the Visual Basic Editor.
2. In the Project window, right-click on the workbook where you want to create the macro and select Insert Module.
3. Paste the following code into the module:
   
   
   Sub CreatePivotTable()
   Dim rng As Range
   Dim pt As PivotTable
   Dim ws As Worksheet
   
   'Change "DataRange" to the name of your data range
   Set rng = ThisWorkbook.Names("DataRange").RefersToRange
   
   'Change "PivotTableLocation" to the location where you want to place the pivot table
   Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets (ThisWorkbook.Sheets.Count))
   ws.Name = "PivotTable"
   Set pt = ws.PivotTableWizard(TableDestination:=ws.Range("A3 "), TableName:="PivotTable")
   
   'Add fields to the pivot table
   With pt
   .PivotFields("Accounting Fund Code").Orientation = xlRowField
   .PivotFields("Amount").Orientation = xlDataField
   End With
   
   'Group the rows in the pivot table
   With pt.PivotFields("Accounting Fund Code")
   .Orientation = xlRowField
   .Position = 1
   .Group Start:=1, End:=4, By:=2
   End With
   End Sub
   
   
4. Save the macro and close the Visual Basic Editor.
5. To run the macro, go to the Developer tab (if it's not visible, go to File Options Customize Ribbon and check Developer in the right-hand pane) and click on Macros. Select the macro and click Run.

This macro creates a new worksheet called "PivotTable" and places the pivot table in cell A3. You can modify the code to suit your needs, such as changing the data range and pivot table location.

Hi,

Am Thu, 6 May 2021 11:58:48 -0500 schrieb dpb:

I don't yet understand much about pivot tables -- I can create one that
has such a sample data set as

B62503
Allied Health - Nursing $2,900.00
Allied Health - RT $1,500.00
Allied Health - ST $1,000.00
Allied Health-Sports Medicine $9,661.48

which is great in that it has everything for the accounting fund code
B62503, but ...

I have to manually collapse the group to get the subtotal and when do
that, the identifying main text goes away so it's "flying blind".

Is there not a way to get a subtotal in the group as well? -- Seems
pretty obvious thing one would want.

I then would like/need a way to autogenerate such pivot tables in a new
workbook instead of also having to create them manually.

1. PivotTable Options = Display and activate "Show expand/collapse
buttons
2. Right click to the groups and choose "Subtotals "Group""

Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgfBF...YM9WQ?e=msZb71


Regards
Claus B.
--
Windows10
Microsoft 365 for business

On 5/6/2021 12:17 PM, Claus Busch wrote:
Hi,

Am Thu, 6 May 2021 11:58:48 -0500 schrieb dpb:

I don't yet understand much about pivot tables -- I can create one that
has such a sample data set as

B62503
Allied Health - Nursing $2,900.00
Allied Health - RT $1,500.00
Allied Health - ST $1,000.00
Allied Health-Sports Medicine $9,661.48

which is great in that it has everything for the accounting fund code
B62503, but ...

I have to manually collapse the group to get the subtotal and when do
that, the identifying main text goes away so it's "flying blind".

Is there not a way to get a subtotal in the group as well? -- Seems
pretty obvious thing one would want.

I then would like/need a way to autogenerate such pivot tables in a new
workbook instead of also having to create them manually.

1. PivotTable Options = Display and activate "Show expand/collapse
buttons
2. Right click to the groups and choose "Subtotals "Group""

Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgfBF...YM9WQ?e=msZb71


Regards
Claus B.


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"

The "Expand/Collapse" buttons are shown; I can toggle them on/off and
when on they work and the total then shows but just the one-line, of course.

The overall grand total at the bottom shows, but no group totals.

I'll try to log on to the community college site and use updated Excel
and see if acts differently there. This is a real bummer as
is...pretty-much makes worthless other than pretty.

--dpb

Hi,

Am Thu, 6 May 2021 13:29:25 -0500 schrieb dpb:

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"

in this case you have another PivotTable under the first one. Insert
rows between the PivotTables that the first one doesn't overlap the
second one if there is one more line per group is inserted.


Regards
Claus B.
--
Windows10
Microsoft 365 for business

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/


Regards
Claus B.
--
Windows10
Microsoft 365 for business

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

Am Thu, 6 May 2021 13:29:25 -0500 schrieb dpb:

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"

in this case you have another PivotTable under the first one. Insert
rows between the PivotTables that the first one doesn't overlap the
second one if there is one more line per group is inserted.

Well, it's another mystery of Excel -- if I try the same thing and put
it on a new worksheet instead of on the existing one besides the data
range where it rightfully belongs, everything seems to work.

I don't understand the difference if the area on the existing sheet is
empty. If it is something about how a pivot table mushes rows together
into the groups that conflicts with other rows on the same sheet, that
also pretty-much makes it useless for this task if will have to keep
moving back and forth between worksheets.

That just won't be at all conducive to the task flow...

I had manually written a similar functionality with conditional
summations but seemed like should be able to get away from having to do
that.

Later I'll see if moving the data range to a brand new worksheet gets
rid of any old "stuff" hanging around behind the scenes from past
attempts just in case, but it looks right now like a non-starter,
unfortunately.

Thanks again for the feedback...

--dpb

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

Hi,

Am Thu, 6 May 2021 16:19:52 -0500 schrieb dpb:

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.

try:
Options = Quick Access Toolbar and add "PivotTable and PivotChart
Wizard"
With that tool you can choose "Multiple consolidation ranges"

or have a look:
https://www.contextures.com/xlpivot08.html


Regards
Claus B.
--
Windows10
Microsoft 365 for business

On 5/6/2021 5:49 PM, Claus Busch wrote:
Hi,

Am Thu, 6 May 2021 16:19:52 -0500 schrieb dpb:

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.

try:
Options = Quick Access Toolbar and add "PivotTable and PivotChart
Wizard"
With that tool you can choose "Multiple consolidation ranges"

or have a look:
https://www.contextures.com/xlpivot08.html


Regards
Claus B.

Thanks...that'll help for overall sanity check. For accounting each
monthly billing has to be posted during the month was accrued so have to
keep those separate -- although on occasion there may be two billings in
a given month, so that could be helpful there, too...although generally
the bookkeeper will enter the two (or more) separately so can check
against the entry for the given month/billing; they're then converted to
values the following month and new billing recorded.

Thank you again, Claus, for the pointers -- did help me figure out what
made no sense to me originally, for sure.

--

On 5/6/2021 5:49 PM, Claus Busch wrote:
Hi,

Am Thu, 6 May 2021 16:19:52 -0500 schrieb dpb:

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.

try:
Options = Quick Access Toolbar and add "PivotTable and PivotChart
Wizard"
With that tool you can choose "Multiple consolidation ranges"

or have a look:
https://www.contextures.com/xlpivot08.html


Actually, I have one more issue that isn't really related to the pivot
table other than it's an artifact of the attempts to try to create one--

When I created a table from the range following some hints I read on one
of the many web sites, Excel "helpfully" inserted alternate-row shading
on top of the existing background fill colors that I don't want to lose.

How can one remove that distracting and now hard-to-read shading without
destroying the other background fill? I can, of course, just trash this
sheet entirely and start over again, but I had done some preliminary
rearranging that would have to redo not knowing what Excel was going to
do to me...

Removing "conditional formatting" didn't have any effect...

--

Hi Duane,

Am Fri, 7 May 2021 09:17:16 -0500 schrieb dpb:

Actually, I have one more issue that isn't really related to the pivot
table other than it's an artifact of the attempts to try to create one--

When I created a table from the range following some hints I read on one
of the many web sites, Excel "helpfully" inserted alternate-row shading
on top of the existing background fill colors that I don't want to lose.

How can one remove that distracting and now hard-to-read shading without
destroying the other background fill? I can, of course, just trash this
sheet entirely and start over again, but I had done some preliminary
rearranging that would have to redo not knowing what Excel was going to
do to me...

select the PivotTable = Design and choose a PivotTable style you like.
You can also play around with "Banded Rows" and "Banded Columns".

Regards
Claus B.
--
Windows10
Microsoft 365 for business

On 5/7/2021 9:54 AM, Claus Busch wrote:
Hi Duane,

Am Fri, 7 May 2021 09:17:16 -0500 schrieb dpb:

Actually, I have one more issue that isn't really related to the pivot
table other than it's an artifact of the attempts to try to create one--

When I created a table from the range following some hints I read on one
of the many web sites, Excel "helpfully" inserted alternate-row shading
on top of the existing background fill colors that I don't want to lose.

How can one remove that distracting and now hard-to-read shading without
destroying the other background fill? I can, of course, just trash this
sheet entirely and start over again, but I had done some preliminary
rearranging that would have to redo not knowing what Excel was going to
do to me...

select the PivotTable = Design and choose a PivotTable style you like.
You can also play around with "Banded Rows" and "Banded Columns".

Regards
Claus B.

This isn't in the PivotTable but in the data range of the PT when I
tried turning it into a table per a suggestion seen on internet.

I turned it back into a regular range from the table and the banded rows
artifact remained. I guess I could try going back to the table again
and seeing if there's some control there.

I can just revert back to the previously saved version; it's not a
terribly large number of modifications I had made so it's not a
disaster, just inconvenience.

Thanks for feedback, as always.

--

Hi Duane,

Am Fri, 7 May 2021 14:42:07 -0500 schrieb dpb:

This isn't in the PivotTable but in the data range of the PT when I
tried turning it into a table per a suggestion seen on internet.

I turned it back into a regular range from the table and the banded rows
artifact remained. I guess I could try going back to the table again
and seeing if there's some control there.

I can just revert back to the previously saved version; it's not a
terribly large number of modifications I had made so it's not a
disaster, just inconvenience.

when you right click on as table formatted data = Convert to range you
will get a normal table. But the colors and the borders remain.
You must select the whole range = no fill and no border.


Regards
Claus B.
--
Windows10
Microsoft 365 for business

On 5/7/2021 2:48 PM, Claus Busch wrote:
Hi Duane,

Am Fri, 7 May 2021 14:42:07 -0500 schrieb dpb:

This isn't in the PivotTable but in the data range of the PT when I
tried turning it into a table per a suggestion seen on internet.

I turned it back into a regular range from the table and the banded rows
artifact remained. I guess I could try going back to the table again
and seeing if there's some control there.

I can just revert back to the previously saved version; it's not a
terribly large number of modifications I had made so it's not a
disaster, just inconvenience.

when you right click on as table formatted data = Convert to range you
will get a normal table. But the colors and the borders remain.
You must select the whole range = no fill and no border.

"That's rude!" :) Afraid of that being a non-reversible "feature".

Fortunately, I do have the original, and had, in fact, already reverted
to it and made the edits again.

Thanks for confirming...

--