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?
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?
