I have an advanced question in regards to Pivot tables that I cannot seem to find an answer for anywhere (if someone can point me in the direction of an already existing thread that would do wonders).
I use my Pivot table (in Data Model mode) to do a summary of my Company financials. I had to use the Data Model, as there are comments in each program that must be shown in the Pivot "value" section.
The issue I have run into is the constant reformatting I must do to the chart due to the subtotal and grand totals concatenating my comments (Mock data attached for reference. Yellow comments are required, but red fields are redundant).
Currently I format those particular cells to show the same background and font color to mask the issue (ie. hide the text), but this is now a file that is accessible to anyone and it is causing unnecessary frustration...
I have tried the following:
- Unselect Subtotal and Grand total
- Problem with this is that I still need the values (ie. profits) to subtotal
- Change font color to match background
- As soon as new data is entered, these formatting is now showing in the wrong cells
- Keep the comments outside of the Pivot chart, and do a Vlookup to pull the comments (ie. keep it separate from the pivot table altogether)
' - The formatting for the report is now unusable since all comments are now at the bottom of the page
Fastest solution would be an option to stop subtotaling on certail fields, but it seems that this option disappeared as soon as the Pivot was made in a Data Model....
Much appreciated for any help.
Thank you
I use my Pivot table (in Data Model mode) to do a summary of my Company financials. I had to use the Data Model, as there are comments in each program that must be shown in the Pivot "value" section.
The issue I have run into is the constant reformatting I must do to the chart due to the subtotal and grand totals concatenating my comments (Mock data attached for reference. Yellow comments are required, but red fields are redundant).
Currently I format those particular cells to show the same background and font color to mask the issue (ie. hide the text), but this is now a file that is accessible to anyone and it is causing unnecessary frustration...
I have tried the following:
- Unselect Subtotal and Grand total
- Problem with this is that I still need the values (ie. profits) to subtotal
- Change font color to match background
- As soon as new data is entered, these formatting is now showing in the wrong cells
- Keep the comments outside of the Pivot chart, and do a Vlookup to pull the comments (ie. keep it separate from the pivot table altogether)
' - The formatting for the report is now unusable since all comments are now at the bottom of the page
Fastest solution would be an option to stop subtotaling on certail fields, but it seems that this option disappeared as soon as the Pivot was made in a Data Model....
Much appreciated for any help.
Thank you
Attachments
Last edited by a moderator:
