Dynamic Chart

[tennisjon2002]

I am doing an excel sheet for work but I am completely stumped on a problem.
I have a sheet with about 950 rows and a few columns showing features and about 10 columns showing a trend in data. I need to filter the data by the features and have the filtered data automatically show in a line graph. The problem is excel will only allow 255 series per graph (so it won't allow me to select all of the data before I filter it.) Right now I have the first 255 rows set in a graph. The problem is when the data is filtered the data stays in its original assigned cell so it will only graph the data that was originially in the first 255 rows. So basically if after the filter there were 2 series and one was originally in row 100 and one was in row 300, only the the one in row 100 shows on the graph. I need the graph to show all of the filtered data.

This is exactly what I want, except I have 950 rows.
http://chandoo.org/wp/2009/02/12/mak...-data-filters/

[Cheeky Charlie]

Short answer: use a pivot table.

Long answer: user a pivot table, upload your data with more explanation of the specific situation and I will be able to show you how.

CC

[tennisjon2002]

One concern I have with a pivot table is updating. This is information that will be updated every month and I will add a new month to the data and take away an old month. With a normal graph it would graph fine since I would just move the data within the selected range to be plotted. Would this work with a pivot table/chart too?

[Cheeky Charlie]

Again, short answer long answer:
Yes.

Yes, I would recommend a "dynamic named range" (google it). Also, in xl03 pivot charts reformat themselves on refresh. If you are fine with the default format it's not a problem. If not, you can simply reference the pivot table with =A1 formulae. I don't know if this is still the case in xl07.

CC

[tennisjon2002]

Here is a better idea of what I have:

Area | Zone | Location | Substation | Circuit # | Jan | Feb | Mar | Apr | May | June.....
X......Y.........Z............A................1.. ...........0......4.......5.......1......2
Z......A.........C............B................2.. ...........1......0.......1.......2......0
. . . . . . . . . .
. . . . . . . . . .

There are 950 rows. I need to be able to filter the circuits by area, zone, substation etc and have the filtered data show in the graph.

I have been messing with pivot tables, but I can only seem to get it to show sums, not each individual value?

[Cheeky Charlie]

Long answer: user a pivot table, upload your data with more explanation of the specific situation and I will be able to show you how.

It's very difficult otherwise.

[tennisjon2002]

It's very difficult otherwise.

I can't upload it, it is stuff for work that can not get out.

[robinterrell]

Absolutely you can use a pivot table. All you have to do is take away the month you no longer need, add the next month. After you have created your pivot table, you can go to "PivotTable Tools" -> Options -> Change Data Source. Then select the data you want. Then you click "Refresh" and it will update both your pivot table and pivot chart. I do this ALL the time on reports.

[Cheeky Charlie]

Yah, redefining the source data can be avoided by using a dynamic named range.

Jon, if you can invest the time to overwrite any critical bits of your spreadsheet I can invest the time to help you.

[tennisjon2002]

Thank you all for all of your help so far. I messed around with the pivot tables and charts some and I have it almost all figured out. The only thing I am stuck on now is the chart showing sums.
For example on this graph:
http://img18.imageshack.us/img18/1378/grapht.png
The series shown is one of the ones on the very top of the chart in the 700-800 range, but the value that I want it to actually graph is only 6? How can I get it to do this?

Here are my pivot table and chart lists:
\1

[tennisjon2002]

bump........

[tennisjon2002]

Anyone????

[tennisjon2002]

sorry pivot table list wasn't working.
Here it is again:
http://img301.imageshack.us/img301/4249/fieldlist.png