Sheet Summary Report Chart

Ross Novotny

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)?

Paul Newcome

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

Paul Newcome

How are you populating the sheet summary fields exactly?

Ross Novotny

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.

Paul Newcome

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?

Ross Novotny

Formula to remove "%"= =SUBSTITUTE(Completeness#, "%", "", 1)

The Source Data is a project timeline.

Paul Newcome

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

Ross Novotny

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

Paul Newcome

Happy to help. 👍️