Creating and maintaining a graph of values from several sheets

Freymish

I want to graph a specific set of values from a number of project sheets. Each project has it's own sheet and there are certain values associated with the project that I want to show as an aggregate graph. So using "Percent Complete" as my example, for 20 projects I'd like to show a graph of the distribution in terms of 0-24%, 25-49%, 50-74%...etc

It's easy enough to grab a count off a single sheet and insert that into a sheet using =COUNT({range}) like this one :

I can't figure out how to accomplsh that result using a report of the percent complete from several sheets. I would prefer to use a report since the target sheets are in a different workspace so that new project sheets get into the report automatically and references don't have to be added to the report as new sheets are are created.

I know I can do this by creating a grid sheet and using the method above but I dan't want my users to have to update the sheet whenever a new project gets added.

Help?

SJ Sellers

I would create a Sheet Summary field for each of those ranges - so 4 fields per each sheet. Then create a Report based on Summary Rows, using the Summarization feature to aggregate the data across the sheets. You would then build your graph from that report.

You would still need to update the report definition each time a new sheet is created to include the new project sheet - but you won't have to update any formulas.

Freymish

I did basically what you suggested.

"You would still need to update the report definition each time a new sheet is created to include the new project sheet"

I found a way around this, though it is a bit kludgy. I created a separate worksace for the project files and reference the workspace in the report. By doing that any new files n the workspace show up in the report automatically. I created a featyre request to enable this for individual folders as well since that makes more sense and is a more flexible solution to this.