How to build analytics for dashboard on a data sheet?

rachel1073

I had a couple of analytics columns on a dashboard that were locked and hidden, where I had a bunch of COUNTIF(S)s and SUMs (as a workaround without pivot tables) to create some charts for my dashboard. Turns out that when people change the sort, these analytics columns got messed up, and now my data is all over the place in those analytics columns, and the SUM functions are broken as a result of that.

 

Can the analytics be in a separate grid, and reference info from the underlying dataset on a separate page? How do I avoid this in the future??

 

EDIT - for example, if I were doing this in Excel, I'd do my analytics on a separate tab in the worksheet and reference the main data set. 

Andrée Starå

Hi Rachel,

It sounds to me like the new Sheet Summary feature would work perfectly for your use case. Have you looked at it?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

More info:

https://community.smartsheet.com/discussion/sheet-summary-0

rachel1073

That can't be displayed on a dashboard though can it?

Andrée Starå

Unfortunately, you're correct! 

At the moment, it doesn't work, but it's coming.

I misread your post.

Sorry about that!

Paul Newcome

Cross sheet references would be perfect for this. You can keep your metrics on a page separate from the underlying data to avoid sorting issues.

 

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

 

In the article they use VLOOKUP as the example walkthrough, but literally every single function supports cross sheet references.