Referencing report data in a sheet/formular

Cecilie · 05/21/24

Hi all

This may be a silly question, but a quick google search seems to say this is impossible; I want to use a report to sum up all tasks overall/done/gone past deadlines, and then use a formular in a sheet to add them all up and be used for a graph. Thing is, it seems I cannot reference a report into a sheet? This feels almost like an oversight - why would I not want to be able to pull all the data from the reports and use it somewhere else?

Right now, each of my project plans has 3 columns which count each task and checks if it has gone past a deadline, or if is checked as 'done'. I am pulling the total of each of those columns into the report. Each project is also born with a 'Status' sheet which pulls the same data, but in order to sum up all tasks of all projects, I have to manually keep adding the refererence links into a formular - which is a little cumbersome - and also easy to forget when you add a new project. The beauty of the report was that it can automatically add a new row whenever a new project gets added to a workspace.

Is there really no way I can total all tasks without having to manually add each new project to a formular?

Stefan · 05/24/24

Hi @Cecilie,

sure, no problem.

First some help info from Smartsheet.

Grouping in Reports: https://help.smartsheet.com/articles/2482082-configure-grouping-to-organize-results-in-report-builder

Summaries in Reports: https://help.smartsheet.com/articles/2482083-summarize-content-to-extract-key-information-with-report-builder

In my example I used the status column for both.

This done, I used the same report in a dashboard widget with only 2 columns, "Status" and "tasks" and the chart type "Donut".

You may have to play around a little with formatting, but this should get you going.

Hope this helps

Stefan

Stefan · 05/22/24

Hi @Cecilie,

did you already try to use the group (status) + count (status) function in the report? If you then use this report in a dashboard with say a ring graph, it shows only the summarised numbers.

Hope I got you right and this helps!

Stefan

eliweitz · 05/22/24

Hi! I might have an idea for a small workaround that could fit the case… Instead of using a report, you could utilize cell linking! Although it doesn't update as quickly, and is definitely a manual process, the results you're looking for are better calculated within a sheet, as reports aren't able to run formulas and calculations. You might also be able to get away with a control center automation (if you have the option of it) that could live-update when new content is provisioned as well. Or you could also create a formula that references corresponding cells in various sheets using the "Reference from another sheet" function too.

Cecilie · 05/24/24

Hi @Stefan

No, I don't know these functions - would it be possible for you to add an image of the solution? (:
It seems it might fix the issue.

Hi @eliweitz

This is actually the current solution we're using! As mentioned, it is becoming a bit too cumbersome to remember adding and removing the links in the formula. I also find that simply writing the link dosen't work, you need to manually open the sheet and find the cell you're looking for each time.

We do have Control Center and I think there might be a solution somewhere there, but I have previously had issues with errors when Control Center encounters a formula - so I'm not confident I can make it work.

Right now our above formula returns Invalid Ref because I don't know how to update the links without re-adding them or removing all of them and then re-adding them.