set up blank pivot tables for future

[ajm]

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?

 

[sandy666]

something like this?

 

[ajm]

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]

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]

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]

maybe just select any cell in Pivot and Insert Pie Chart ?

 

[ajm]

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]

create Style from existing Pie Chart and use it for next blank charts, imho it should work

 

[ajm]

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.

 

[sandy666]

maybe try Pivot Chart Style