set up blank pivot tables for future

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,732
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?
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,732
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?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,292
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
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,732
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?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,292
maybe just select any cell in Pivot and Insert Pie Chart ?

 
Last edited:

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,732
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....
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,292
create Style from existing Pie Chart and use it for next blank charts, imho it should work
 
Last edited:

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,732
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.
 

Forum statistics

Threads
1,078,273
Messages
5,339,190
Members
399,288
Latest member
ossa

Some videos you may like

This Week's Hot Topics

Top