Macworld Forums

Macworld Forums: Excel Pivot Chart won't refresh - Macworld Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Excel Pivot Chart won't refresh

#1 User is offline   gw1500se 

  • Newbie
  • Pip
  • Group: Members
  • Posts: 10
  • Joined: 01-August 05

Posted 01 August 2005 - 07:07 AM

I have a pivot table from which I generated a chart. When I refresh my data the pivot table refreshes fine. However, the chart will not refresh. By that I mean if the pivot table adds rows, those added rows must manually be added to the chart range. Can someone help me automate this? TIA.
0

#2 User is offline   shades 

  • Member
  • PipPip
  • Group: Members
  • Posts: 367
  • Joined: 28-June 02

Posted 01 August 2005 - 09:32 AM

Yep, sadly MS does not support Pivot Charts on the Mac side.
One way to do this is to define dynamic names so that the name adjusts automatically for the source data.
For instance, with heading in cell B4, and your data begins in cell B5 [hence, the reference to B5 in the OFFSET formula below), then define the X-Axis name. Go to Insert > Name > Define Name, type a name (no spaces) in the box, like MyCities and in the Refers to box, put this in:
=OFFSET(Sheet1!$B$5,1,0,COUNTA(Sheet1!$B:$B)-1,1)
Click ADD, then to check it, click in the refers to box, and see what is highlighted; it should be just the rows that are filled. As you add names to the Colmn B data, this formula will automatically adjust.
Then add another name, this time define the name as MyAmount, and in the refers to box, put this [It offsets one column to the right]:
=OFFSET(MyCities,0,1)
Click Add, and Close the dialog box.

Now, select the chart, CTRL + Click the chart and choose Source Data. In the resulting dialog box, choose the second tab, Series, in the Values box put this [WorkbookName is whatever your workbook has been named]:
=WorkbookName.xls!MyAmount
and in the Category X box at the bottom, put this:
=WorkbookName.xls!MyCities.
Click OK.
Now, when you add a name to column B and an amount in column C, the chart will automatically add it.
If you have more data series (Columns D, E, etc.), then define each one with a dynamic name just as you did for MyAmount (i.e. as an OFFSET from MyCities), but adding 1 to the offset value each time.
HTH
0

#3 User is offline   gw1500se 

  • Newbie
  • Pip
  • Group: Members
  • Posts: 10
  • Joined: 01-August 05

Posted 01 August 2005 - 09:36 AM

I'm not and Excel expert so I hadn't thought of that. Its a good workaround. Thanks.
0

#4 User is offline   gw1500se 

  • Newbie
  • Pip
  • Group: Members
  • Posts: 10
  • Joined: 01-August 05

Posted 02 August 2005 - 03:16 AM

After some playing around with this I am not able to get what I need. It was probably poor explaination due to the fact I was not aware pivot charts were not supported. I don't believe I can accomplish what I want without a pivot table. Anyway, here is a more explicit explaination.
I have 3 columns of data. The first column is a date field, the second is a name string and the third is an integer. For each name string (series), I need to plot the date along the x-axis and the integer on the y-axis as a line. With the pivot table, I had the chart just the way I needed it except it would not refresh. With your suggestion I only get a single line of some unknown combination of the integer data for each date. I'm sure I need to put the pivot table back but I can't seem to get it right using the symbolic names. TIA.
0

#5 User is offline   gw1500se 

  • Newbie
  • Pip
  • Group: Members
  • Posts: 10
  • Joined: 01-August 05

Posted 02 August 2005 - 04:07 AM

I think I am now making some progress. I have my pivot table defined using name definitions as you suggested for the chart. I now think I know how to set up the chart from the pivot table using similar names but I have one problem. Your examples showed me how to define the offset using COUNTA to set up the column height. However, I also need to set up the row width and I cannot seem to come up with the right syntax for that and the documentation for COUNTA does not have an example. To get my chart I need to define the series which is B2 through however many columns there are in row 2 of the pivot table (=OFFSET(Mytable!$B$2,0,0,0,COUNTA(??,??))). The data starts in column B3 and goes to whatever number of rows and columns that exist in the pivot table (=OFFSET(Mytable!$B$3,0,0,COUNTA(Mytable!$B:$B),COUNTA(??,??))).
Thanks again.
0

#6 User is offline   shades 

  • Member
  • PipPip
  • Group: Members
  • Posts: 367
  • Joined: 28-June 02

Posted 02 August 2005 - 05:43 AM

Very close:
=OFFSET(Mytable!$B$2,0,0,1,COUNTA($2:$2))
In other words, for the column count, use the top row (row 2) and have the formula count how many elements appear in Row 2. Now you may have to adjust that number depending on how you have it set up. For instance, you may have to use this (meaning that you subtract 1 from the column count -experiment to see what gives you the desired result):
=OFFSET(Mytable!$B$2,0,0,1,COUNTA($2:$2)-1)
0

#7 User is offline   gw1500se 

  • Newbie
  • Pip
  • Group: Members
  • Posts: 10
  • Joined: 01-August 05

Posted 02 August 2005 - 09:53 AM

That got it. Thanks.
0

#8 User is offline   aliceho 

  • Newbie
  • Pip
  • Group: Members
  • Posts: 1
  • Joined: 18-January 06

Posted 18 January 2006 - 02:03 AM

Hello, i am new at Mac. I wonder if you know how to create a PivotChart..Please, guide me on this..thanks
0

#9 User is offline   shades 

  • Member
  • PipPip
  • Group: Members
  • Posts: 367
  • Joined: 28-June 02

Posted 18 January 2006 - 09:58 AM

Howdy.
If you read my post dated: 08/01/05 12:32 PM you will note that in my first line Macs do not support PivotCharts.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users