Archive of Mr Excel Message Board

Had an idea, can you tell the graph to ignore blanks?

Ben, are you sure that you've selected the entire
PivotTable before creating your graph? If you
haven't the chart won't dynamically adjust as the
PivotTable content changes. To be sure that you've
selected the entire PivotTable I recommend that you
use the "Select Label and Data" selection tool which
you may need to add to your PivotTable toolbar.

By the way, what ever happened on your earlier
posting about rows that disappeared when you
changed your PAGE field?

Mark

The answer is yes, I have selected the entire table before i created the table. But what I think might mess the dynamically changing series is that I've split up the graph.

Let me explain:-Basically I created the pivot table with,
Rows of "Date-Hour"
Cols of "Group"
Page of "Band"
And filled with sum of values.

I then created a graph of the entire Pivot table.
I need to split the data so I would have one graph for each week (my data has 5 weeks of "date-hour" data) So I tried pointing the graph at the relevent week, but it didn't like the pivot table. So I build a macro that changed the series pointers and it gave me the first weeks graph. & the i did the same for the second week. & so on.

But this way looses the dynamically changing series. Have I done the wrong thing if i need to select parts of pivot tables?

Is it possible to write a bit of VBA that take the Legend and hides the "blank" series? I know its not perfect but its an answer.

By the way I sorted out my problem with "rows disappearing, thanks for asking. It was a box I had'nt seen!! Typical.

Thanks, and I hope its clear!!!

Ben, can you insert a week column (or a
Week-Date-Hour column) into your data
set and then modify your PivotTable to
include it?

Mark

I've added a new variable "week" (values 1-5) to my initial data, So now I've got a pivot table with "week" & "date-hour" as rows, "group" as columns & "band" as page.( and my data summed)

So can I build dynamically changing graphs for each week?

Thanks

Ben

Yes... each category on your X-axis would be a
an unique instance of 'Week' and 'Date-Hour'. Is
that what you want? I've added a new variable "week" (values 1-5) to my initial data, So now I've got a pivot table with "week" & "date-hour" as rows, "group" as columns & "band" as page.( and my data summed) So can I build dynamically changing graphs for each week? Ben

MArk

Could I have 5 seperate graphs (1 for each week?)

Ben

Sure! You could create a PivotTable of each of
your5 weeks and generate separate charts from each
PivotTable. MArk Could I have 5 seperate graphs (1 for each week?) Ben