Crosslinking cells to reports or summary sheets?
Hello SmartSheet Community!
I'm trying to find a solution to a few problems, but if I could crosslink 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 celllink 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 realtime 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).
Best Answer

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
Answers

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

Thank you Matt! This is exactly what I needed!
Help Article Resources
Categories
Check out the Formula Handbook template!