Thread: Dynamic Chart

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/

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

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?

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

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?

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.

Originally Posted by Cheeky Charlie

It's very difficult otherwise.

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

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.

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.

bump........

Anyone????

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