set up blank pivot tables for future

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
folks,

I am trying to set up a model that will report for the next twelve months. Each month is on a separate worksheet. I need to show salesperson and sales amount in one of the graphs. Given that sales people may or may not achieve a sale in any one month, I thought that using a pivot table would be the best basis for the pie graph. This works for the current month where we already have sales but does not work for future months where i only have the headers. I started to set up dynamic named ranges to hold the data which will be generated each month but for some reason they are not working. To get around the empty data set I had included the column headers, but excel needs two rows of source data for a pivot table.

Is there a way to trick the pivot table into accepting the named range as its data source without adding dummy data that the users will most likely forget to delete? Better yet, has anyone got a better way of handling this?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
something like this?

screenshot-122.png
 
Upvote 0
yes. I was just about to post back on my own query to say that I had a workaround sorted out. That is, for each month that there is yet no data, (December > June), I just added a 0 (zero) to the first row of sales data. Is that what you have done?
 
Upvote 0
no, I made Headers, select headers and one row below then use Ctrl+T (Excel Table) and create Pivot from this, no any value in empty row
 
Upvote 0
easy solution. cheers. Now I just need to set up the pie graphs to read from the empty tables and off we go. any ideas before I post a new thread?
 
Upvote 0
maybe just select any cell in Pivot and Insert Pie Chart ?

screenshot-123.png
 
Last edited:
Upvote 0
Thanks Sandy, thats what I have done. I should have been more specific. I want the pie chart to be the same on each tab. so while I can make a place for it, i can't add labels, and format it as there is no data. If i do a dummy row on each monthly worksheet, i will be able to add the data labels. then delete the dummy data and i should be left with a pie chart that will "automatically" create itself when the end user enters data. Its very convoluted and i have a full year's worth with three per month to add. my head hurts....
 
Upvote 0
create Style from existing Pie Chart and use it for next blank charts, imho it should work
 
Last edited:
Upvote 0
i have tried to create the graphs on the one sheet for October and then, using it as a template, create each new month and rename accordingly. I thinks its because the chart is a pivot chart, the Chart Data Range is unable to be edited.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top