Options
azerty64 ✭✭
05/19/22 in Formulas and Functions
Hello everyone,
I'm french so sorry for my english...
I want to return the city name if the "Territory" column contains T1 or T2... Knowing that I use a correspondence sheet: T1 = PAU; T2 = Toulouse; ....
=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4}; HAS(Territoire@row; @cell)); "-"))
My current formula only works if the searched value is exact (T1) but as you can see, this is not always the case (T1 - Mont).
Can you help me please
Thank you Google Translate..
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/20/22 edited 05/20/22 Answer ✓
Options
Try his:
=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4}; @cell = LEFT(Territoire@row; FIND(" "; Territoire@row) - 1)); "-"))
1 · Share on FacebookShare on Twitter
Answers
James Keuning ✭✭✭✭✭
05/19/22
Options
Instead of
HAS(Territoire@row; @cell)try
Territoire@row0 · Share on FacebookShare on Twitter
azerty64 ✭✭
05/20/22
Options
https://community.smartsheet.com/discussion/comment/330227#Comment_330227
Hello,
Thank you for your answer. But unfortunately it doesn't work
0 · Share on FacebookShare on Twitter
Toufong Vang ✭✭✭✭✭
05/20/22
Options
(1) What should happen when the value in "Territoire" is Toulouse, Pau, or something else?
(2) When the value in "Territoire" contains something like "T1-MONT" or "T3-SVT", will it always begin with "T1", "T2", "T3", "T4", etc.?
0 · Share on FacebookShare on Twitter
azerty64 ✭✭
05/20/22
Options
https://community.smartsheet.com/discussion/comment/330462#Comment_330462
Hello,
(1) if the value = Toulouse then Toulouse
(2) Yes it will always start with T1, T2, T3, T4 etc.
Thank you
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/20/22 edited 05/20/22 Answer ✓
Options
Try his:
=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4}; @cell = LEFT(Territoire@row; FIND(" "; Territoire@row) - 1)); "-"))
1 · Share on FacebookShare on Twitter
Toufong Vang ✭✭✭✭✭
05/20/22
Options
Try this...
=IF(FIND(LEFT(Territoire@row,2),"T1,T2,T3,T4") = 0, Territoire@row, JOIN(COLLECT({City name Plage 3}, {Correspondance Territoire Plage 4}, @cell = LEFT(Territoire@row, FIND(" ", Territoire@row) - 1)), "-"))...replaced the logical expression in @Paul Newcome's formula with
FIND(LEFT(Territoire@row,2),"T1,T2,T3,T4")=0to check if the first two letters of the value in Territoire is in the string "T1,T2,T3,T4".0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/20/22
Options
@Toufong Vang The original post indicated that we needed to only search for the "T##", so having "T1 - SVT" is throwing things off.
Your formula is going to always throw out a 0 for that first FIND function because there is not a cell containing "T1, T2, T3, T4". The FIND function throwing a 0 means it will output the same data that is already in the Territoire cell every time. This is not what the original post asked for.
The original post asked to do the search based on "T##" regardless of whatever else was in the cell. The method I used pulls the desired string to search against and accommodates other variations such as "T5" or "T98765".
@azerty64 I have also realized that I forgot to include a bit in case it is just "T1". Please see below:
=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4};@cell = IFERROR(LEFT(Territoire@row; FIND(" ";Territoire@row) - 1); Territoire@row); "-"))
0 · Share on FacebookShare on Twitter
azerty64 ✭✭
05/23/22
Options
https://community.smartsheet.com/discussion/comment/330470#Comment_330470
Hello @Paul Newcome,
Thanks, it works
And thanks to everyone for trying to help me.
Have a good day.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/23/22
Options
@azerty64 Happy to help. 👍️
Also keep in mind that there is a possibility that if the cell contains just "T1" (or any other number but no additional text), my first formula may throw an error. My last one has been updated to account for those.
0 · Share on FacebookShare on Twitter
azerty64 ✭✭
05/31/22
Options
https://community.smartsheet.com/discussion/comment/330633#Comment_330633
Thanks a lot @Paul Newcome !
But if it is indeed this formula:
=IF(ISBLANK(Territoire@row); ""; JOIN(COLLECT({City name Plage 3}; {Correspondance Territoire Plage 4};@cell = IFERROR(LEFT(Territoire@row; FIND(" ";Territoire@row) - 1);Territoire@row); "-"))
I can't get it to work. Is there a mistake or is it me ? 😁
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/31/22
Options
@azerty64 It looks like you missed a closing parenthesis there close to the end. There should be a total of 2 before that last ;.
0 · Share on FacebookShare on Twitter
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
- 59.3K Get Help
- 88 Global Discussions
- 92 Industry Talk
- 398 Announcements
- 12 Community Corner Newsletter
- 71 Brandfolder
- 121 Just for fun
- 42 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 250 Events
- 13 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
