Cross-linking cells to reports or summary sheets?

KarenTF

Hello SmartSheet Community!

I'm trying to find a solution to a few problems, but if I could cross-link cells to summarized fields in a report or sheet summary, it would solve my problems, but it doesn't appear to be an option. So first question: Is there a way to cell link to a summarized field in a report or sheet summary?

If so, great! Please inform me how to do this.

If not, here is what I'm trying to accomplish and maybe someone can give me a good direction. I want to create chart widgets for a dashboard, but the only way I can get the chart to display the way I want is to setup a separate empty sheet and manually input the averaged data. I'd like to be able to use a formula to pull in that averaged data point based on multiple conditions from the main sheet. For instance, in this separate working sheet for the widget, I would want a cell-link to average the Total Points from the main sheet WHEN the Quarter is equal to "Q1" AND the Division is equal to "D1". That way I can set up the same formula for each Division/Quarter pair to have all the averages populating in real-time to this working sheet, which will then populate my chart widget in the display that I am trying to achieve (because the charts offered from the reports aren't displaying how I need them to).

Matt Johnson

Hi @KarenTF

You are on the right path with the separate empty sheet (aka metric sheet). I consider metric sheets to be a free form area where everything is pulled in with cross sheet formulas. I rarely use cell links because formulas allow for variations with comparisons of data. For what you are describing, you could set it up a few different ways depending on how you want the graph to look. The formula would be something like

=AVG(COLLECT({Total Points}, {Division}, "D1", {Quarter}, "Q1"))

Depending on how you want the graph to be you could set up a table for each Division and list the Quarters down a column on the left and then replace the "Q1" with the value to the left@row and then drag the formula down. Lots of options there.

I hope that helps.

Matt

Matt Johnson

Hi @KarenTF

You are on the right path with the separate empty sheet (aka metric sheet). I consider metric sheets to be a free form area where everything is pulled in with cross sheet formulas. I rarely use cell links because formulas allow for variations with comparisons of data. For what you are describing, you could set it up a few different ways depending on how you want the graph to look. The formula would be something like

=AVG(COLLECT({Total Points}, {Division}, "D1", {Quarter}, "Q1"))

Depending on how you want the graph to be you could set up a table for each Division and list the Quarters down a column on the left and then replace the "Q1" with the value to the left@row and then drag the formula down. Lots of options there.

I hope that helps.

Matt

KarenTF

Thank you Matt! This is exactly what I needed!