How to populate a column with single line summaries of data from another sheet
A picture says a thousand words and I have struggling on how to explain what I am trying to do with a picture.
Top left corner there is a list of people who have submitted data via a form. It is possible for the same person to make multiple submissions. On a different grid (bottom left corner), I am totaling each persons submissions to give them a single total value. So, for example, Bob bought 25 widgets.
Now, in the top right corner, a new person (Henry), submitted an order for 18 widgets, and I would like to add add Henry's orders to the summary. The problem is that I did not know Henry was going to submit an order nor do I know in advance of anyone else.
Is there a way that Smartsheet can read the column of "names", identify each 'distinct' name, and then automatically add that name to the list on the summary grid? I would then ask Smartsheet to perform a simple =sumif function to get the total number of widgets for Henry?
I am trying to save the hassle of going through the entire list of names to isolate each 'distinct' name and then adding that name manually to the summary. If Smartsheet could say, "oh, this is a new name, I will add it to the list" that would be fantastic.
I know there is the grouping and summary functions in Reports but that does not work with what I need to do.
Thanks in advance.
Ryan
Best Answers
-
You can set up a helper checkbox column that will mark each row that marks unique names. You can then use a copy row automation to grab that row and then hide all columns except for the name column. From there you can use a SUMIFS with cross sheet references as a column formula to pull the total counts.
-
Happy to help. 👍️
Answers
-
You can set up a helper checkbox column that will mark each row that marks unique names. You can then use a copy row automation to grab that row and then hide all columns except for the name column. From there you can use a SUMIFS with cross sheet references as a column formula to pull the total counts.
-
Great solution. I knew there had to be a simple solution and what you suggested will be very easy to do. I have not used the copy row automation very much and this is a great example for it. Thank you!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!