Pivot-Nightmare ;-)

Posted by Lukas Weder on January 08, 2002 3:20 PM

I've got a tricky problem. I created a Pivot Table based on an external datasource (MS Access table).I want to create a chart from this Pivot Table which automatically "grows" with the Pivot Table. In the column-field I have the date (by month). So every month my Pivot Table adds a new column with that new month (because of new data entries in the access table). Unfortunately the selection for the chart doesn't "grow" automatically. So I defined names for the date and the data (ex."date" =OFFSET(sheet1!$A$1,0,0,,COUNTA(sheet1!$1:$1)-1)) and tweaked the series to =series(sheet1!date,sheet1!data,1). Unfortunately there are still always problems with updating that chart. Why doesn't it work? Is this a bug in Excel 97? Do you have a solution for this problem?

Thanx a lot for your answer!

Lukas Weder

Posted by paul on January 08, 2002 7:18 PM

If you can upgrade to Excel 2000 - there is a pivot table chart function that does this for you. I dont know of a solution in 97 without a macro to search the data set.

Posted by Lukas Weder on January 09, 2002 5:59 AM

Thank you PJ!

Unfortunately I'm a beginner in VB. How would you get started?

Lukas

Posted by Mark w. on January 09, 2002 7:34 AM

Lukas, you can create a Chart directly from
the PivotTable that will dynamically change
when the PivotTable does; however, there are
are couple of tricks that you need to know
about. First, you need to turn off Grand
totals for columns and rows, and second, you
must use the Select | Entire Table command
from the popup menu produced by right mouse-
clicking on the PivotTable itself.

Posted by Lukas Weder on January 09, 2002 8:13 AM

Thank you. I tried to do it this way... But with Excel 97 it doesn't work. Microsoft solved this bug in version 2000. Unfortunately I need to do it with Excel 97. There is no other way than with VB. May you know how to do it with VB?

Lukas

Posted by Mark W. on January 09, 2002 9:05 AM

> But with Excel 97 it doesn't work

Yes, it does. In fact, I did it as I wrote my
reply in order to re-familiarize myself with the
steps. I can't emphasize enough the importance
of Selecting [the] Entire Table (see my tip below)
before launching the Chart Wizard.