Sheet Summary Report Chart
I continue to get an error when I try to create a chart of a Sheet Summary Report. The error states "Selected Data can not be charted. Please select a new range". The data is pretty straightforward, its a list of 15 locations with a completeness between 0 and 100. I tried removing the percentage in my sheet summary formula, but the result is the same. Does anyone know why I wouldn't be able to chart the data in a bar graph (or any graph)?
Best Answer

The SUBSTITUTE function outputs text data. Try wrapping it in a VALUE function, dividing by 100, then formatting the field as a percentage.
Answers

How are you populating the sheet summary fields exactly?

Im populating with a very long index/match formula in each of the sheets. That index/match formula populates a % of completeness. I also added a sheet summary formula to remove the "%" to see if that would work, that is simply a substitute formula. Neither seem to work.

Are you able to provide the formula that removes the "%"?
How is the source data that is being pulled in by the INDEX/MATCH being populated?

Formula to remove "%"= =SUBSTITUTE(Completeness#, "%", "", 1)
The Source Data is a project timeline.

The SUBSTITUTE function outputs text data. Try wrapping it in a VALUE function, dividing by 100, then formatting the field as a percentage.

You're an amazing Smartsheet detective. That worked, thanks Paul.

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!