Excel Pivot Chart won't refresh
Posted 01 August 2005 - 07:07 AM
Posted 01 August 2005 - 09:32 AM
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:
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]:
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]:
and in the Category X box at the bottom, put this:
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.
Posted 02 August 2005 - 03:16 AM
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.
Posted 02 August 2005 - 04:07 AM
Posted 02 August 2005 - 05:43 AM
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):