Options
Chamilla ✭✭
01/09/20 in Formulas and Functions
I need to create a formula that concatenates (JOIN or "+") several fields in my sheet summary, back into a column in my sheet. What is the syntax I need to use to be able to reference sheet summary fields?
Thanks!
Tags:
- Formulas
- functionality
- Sheet Summary
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/09/20 Answer ✓
Options
To reference a Sheet Summary field, you enter the field name using the same rules as regular column labels (square brackets for spaces, numbers, etc) and use # in place of the row number.
[Summary Field]#
6 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/09/20 Answer ✓
Options
Unfortunately as of right now the only way to get multiple contact in a cell as actual contacts is to manually enter them. There is no way to use a formula to pull multiple contacts in a cell.
That would be great though. Feel free to Submit a Product Enhancement Request.
5 · Share on FacebookShare on Twitter
Answers
Paul Newcome ✭✭✭✭✭✭
01/09/20 Answer ✓
Options
To reference a Sheet Summary field, you enter the field name using the same rules as regular column labels (square brackets for spaces, numbers, etc) and use # in place of the row number.
[Summary Field]#
6 · Share on FacebookShare on Twitter
Chamilla ✭✭
01/09/20
Options
Thanks, Paul! Now, a new challenge... I'm using the "+" formula to concatenate several contact list type fields from the sheet summary into a contact list type field in the sheet. I checked the "Allow multiple contacts per cell" box, however, the results of this formula are showing as text type and not contact list type. I need the names to be contacts as I plan to use this cell in an automation workflow to direct an alert to this group of contacts.
Thanks again!
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/09/20 Answer ✓
Options
Unfortunately as of right now the only way to get multiple contact in a cell as actual contacts is to manually enter them. There is no way to use a formula to pull multiple contacts in a cell.
That would be great though. Feel free to Submit a Product Enhancement Request.
5 · Share on FacebookShare on Twitter
Chamilla ✭✭
01/09/20
Options
Agreed. I'll submit the request. Thanks again, Paul.
Chamilla
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
01/09/20
Options
Happy to help! 👍️
0 · Share on FacebookShare on Twitter
Jason Wirl ✭✭
05/08/20
Options
@Paul Newcome
Is it possible to use this syntax to get the data from a Sheet Summary field ON ANOTHER SHEET? I know you can reference another sheet's data in the sheet sheet itself, but I can't see a way to get Sheet Summary data.
Jason
2 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
05/08/20 edited 05/08/20
Options
Hi @Jason Wirl,
Unfortunately, it's not possible at the moment to collect information from another sheets Sheet Summary, but it's an excellentidea!
Pleasesubmitan Enhancement Request when you have a moment
You'll have to add the data from the Sheet Summary to somewhere else in the sheet.
Would that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
1 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/08/20
Options
No. It is currently not possible to access another sheet's Summary Fields through cell linking or cross sheet references.
For something like this, I usually add a column to the sheet I want to reference, use a basic cell reference to pull the data from the Summary Field(s), then hide the column. This puts the data in the sheet so that I can access it via cross sheet references/cell links and keeps the sheet looking clean.
Feel free to Submit a Product Enhancement Request when you have a moment in regards to accessing Sheet Summary Fields through cell linking/cross sheet references.
2 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/08/20
Options
@Andrée Starå Looks like we were typing at the same time yet again. Hahaha. I can't remember if it was you or maybe Mike or Luke that made a suggestion of having a "typing" indicator when someone else is working on a comment.
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
05/08/20
Options
@Paul Newcome
Haha! Yes, I've suggested that.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
Jason Wirl ✭✭
05/08/20
Options
@Paul Newcome @Andrée Starå
Thanks fellas. Appreciate the suggestions, especially work-arounds 😉. I'm a big believer in them.
Cheers,
Jason
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/08/20
Options
@Jason Wirl Happy to help! 👍️
1 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
05/08/20
Options
@Jason Wirl
You're more than welcome!
Workarounds, make the world go round!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
Jake Gustafson ✭✭✭✭✭✭
05/18/21
Options
Has there been any changes in the ability to reference Sheet Summary fields in another sheet?
My use case is this. I have various sheets that I have a Summary Field with the total number of records. I want to make sure all my sheets have that number of records as I'm using different sheets to roll up various metrics. Some of the inputs have occasional new values that I want to make sure I'm accounting for. Until the Dashboards support Grouped Reports, I need a cleaner way to represent my summary/trend data. Was hoping to reference 6 sheets worth of Summary fields then add some formulas to indicate if there are any mismatches, then I could setup an automation to alert me of the mismatch and go address it accordingly.
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
05/18/21
Options
Hi @Jake Gustafson
I hope you're well and safe!
No, not really. There are workarounds, but the good news is that the Grouping / Summarize support is released for dashboards now.
Would that work/help?
I hope that helps!
Be safe and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
«12»
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59K Get Help
- 85 Global Discussions
- 87 Industry Talk
- 396 Announcements
- 11 Community Corner Newsletter
- 68 Brandfolder
- 120 Just for fun
- 41 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 235 Events
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
