Is there a way to reference a summary field from one sheet in another sheet?
I have sheet that contains master data for customers. The master data customer sheet has a set number of support hours available for each customer. I have another sheet that is collecting support hour information for the customers. A summary field has been created for each customer that sums the support hours for that customer.
Is there a way to reference the summary field containing support hours for the customer in a formula in the master data sheet? I would like to build a report or dashboard that would show the available support hours for each customer (Total support hours available - sum of customer support hours used = total available customer support hours).
Answers
-
Andrée Starå ✭✭✭✭✭✭I hope you're well and safe!
Yes, you can use cross-sheet formulas in the Sheet Summary too. Another option would be to add so-called helper columns or select a sheet section and link the Sheet Summary field to that area and then use cell-linking to collect everything in a Master Metrics Sheet.
Make sense?
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅了我的帖子(s)帮助或回答你的问题吗tion or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Hi Andree,
Thank you. I am well and hope you are the same.
This is helpful, but I could not find anything in the Help / Support documents that showed how to reference the summary fields from a syntax perspective. In particular, how to reference the sheet and column needed.
Regarding the second option, the helper columns, I am unclear how that could be implemented in my situation. If you could point me to a reference document, that would be appreciated.
Thanks,
Matt
-
Hem Shah ✭
Hi Matt,
Thank you for asking this question. Did you ever get around to the answer on how to reference sheet summary from a different sheet? I've been looking around for a bit but haven't found anything either.
Wondering how you got around it!
Thanks,
Hem
-
Thanks for getting back to me, but I did not find a solution.
Thanks,
Matt
Categories
This method does produce separate reports:<\/p>
The Project Plan report will include this filter: \"Sheet Type\" is one of \"Project Plan\" -- this filter will exclude rows coming from the RAID Log (and all other types of sheets) because those other sheets will have a value in their \"Sheet Type\" column that is not \"Project Plan.\" <\/p>
The RAID Log report will include this filter: \"Sheet Type\" is one of \"RAID Log\" -- this filter will exclude rows coming from the Project Plan (and all other types of sheets) because those other sheets will have a value in their \"Sheet Type\" column that is not \"RAID Log.\" <\/p>
Thus, each report will include a filter rule specifying that only rows with its own sheet type can be included.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":265,"urlcode":"Reports","name":"Reports"}]},{"discussionID":108417,"type":"question","name":"How do i set up a conditional format, when i want more than one person in the assigned to column","excerpt":"I have each assigned to person with conditional format with a color to let them know what they are assigned to work, but what if i need two or more associates to work that particular assignment, how do i set up conditional formatting for that, or would that be a formula?","snippet":"I have each assigned to person with conditional format with a color to let them know what they are assigned to work, but what if i need two or more associates to work that…","categoryID":321,"dateInserted":"2023-08-02T14:26:40+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T15:27:36+00:00","insertUserID":159629,"insertUser":{"userID":159629,"name":"Genesia","url":"https:\/\/community.smartsheet.com\/profile\/Genesia","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T20:20:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":159629,"lastUser":{"userID":159629,"name":"Genesia","url":"https:\/\/community.smartsheet.com\/profile\/Genesia","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T20:20:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3381977656,"url":"https:\/\/community.smartsheet.com\/discussion\/108417\/how-do-i-set-up-a-conditional-format-when-i-want-more-than-one-person-in-the-assigned-to-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108417\/how-do-i-set-up-a-conditional-format-when-i-want-more-than-one-person-in-the-assigned-to-column","format":"Rich","tagIDs":[245,254,334,437],"lastPost":{"discussionID":108417,"commentID":388509,"name":"Re: How do i set up a conditional format, when i want more than one person in the assigned to column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388509#Comment_388509","dateInserted":"2023-08-02T15:27:36+00:00","insertUserID":159629,"insertUser":{"userID":159629,"name":"Genesia","url":"https:\/\/community.smartsheet.com\/profile\/Genesia","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T20:20:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9VLKF1G9AO1Q\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:17:11+00:00","dateAnswered":"2023-08-02T14:42:44+00:00","acceptedAnswers":[{"commentID":388483,"body":"
