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.
-
Help Article Resources
Categories
Check out the Formula Handbook template!
