Tricky Pivot Table Question

Posted by Lukas Weder on January 04, 2002 11:13 AM

I created a Pivot Table with an external datasource (access table). In the column field I have the calendar date (Jan-00 till Dec-01). If I add some Jan-02 records to my access table, the Pivot table "grows" and I see a new Jan-02 column... so far so good... I created a chart with the Pivot Table as sourcedata, but unfortunately the chart doesn't "grow" like the Pivot Table and there is no Jan-02 shown up.

Do you have any idea how the chart can be automatically updated? (I use MS Excel 97)

thanks a lot!

Lukas Weder

Posted by Adam S. on January 04, 2002 11:25 AM

Question:

Hiya

Just to be clear, in your example would the new Jan-02 column represent a new data series in your chart? Or would it be an extention (another point) for each row?

Posted by Lukas Weder on January 04, 2002 11:30 AM

Re: Question:

thanx for the question.
it's not a new data series. it's just another point for each row...

Posted by Adam S. on January 04, 2002 11:59 AM

Do this

Hey again.

Ok cool. You can use named ranges to automate the chart. What I have below assumes there is no other data in the same rows as what's being charted (it may need to be tweaked otherwise).

Sample view of an example data source:

Say your table pulls dates into row 2 (starting with the Header "Date" in C2. Data for these dates I assume are directly under (C3 contains the word "Data1" for the first data series).

Go to: Insert\Name\Define

Define Date as:
=OFFSET(Sheet1!$D$2,0,0,,COUNTA(Sheet1!$2:$2)-1)

Define "Data1" as:
=OFFSET(Sheet1!$D$3,0,0,,COUNTA(Sheet1!$3:$3)-1)

Now go to the chart and tweak the series to:
=SERIES(,Book1!date,Book1!data,1)

You should be set.

Hope that helps
Adam S.

Posted by Adam S. -nt on January 04, 2002 12:06 PM

typo: Series is =SERIES(,Sheet1!date,Sheet1!data1,1)

Posted by Lukas Weder on January 04, 2002 1:25 PM

Re: typo: Series is =SERIES(,Sheet1!date,Sheet1!data1,1)

Cool, that seems to be the perfect solution for my problem... THANK YOU!!!

I defined the date and the data1... afterwards I wrote =series(,sheet1!date,sheet1!data1,1) in the "VALUES:"-field... I got the following error-message:

"The series must contain at least one value in order to create a chart"

What did I wrong???

Thanx again

Lukas Weder

Posted by Adam S. on January 04, 2002 1:49 PM

Some things to check

Hey again

You are *this* close!

I suspect the error you received had something do do with one or both of your OFFSET formulas.

I had used:

Define Date as:
=OFFSET(Sheet1!$D$2,0,0,,COUNTA(Sheet1!$2:$2)-1)

Above:D2 represents the cell with the first actual date (I assume you already tweaked "Sheet1!" to the name of your own sheet). If your first date in in G2, modify the formula above to reflect it.

Define "Data1" as:
=OFFSET(Sheet1!$D$3,0,0,,COUNTA(Sheet1!$3:$3)-1)

Same as above: D3 in my example represents the first data1 value (not the variable name-which is assumed to be in cell C3).

I might have accidentally described the cell locations incorrectly the first time around. (I hope not).

Now go to the chart and tweak the series to:
=SERIES(,Sheet1!date,Sheet1!data1,1)

This is the series formula in the chart itself. In my example I've ignored the first argument (for a chart title). The Book1! reference from before reflected the filename I was playing with to verify my answer - excel overwrote my sheet1! refs by itself.

Good luck
Adam S.