Options
Michael W. ✭✭✭✭
05/12/21 edited 05/12/21 in Formulas and Functions
Greetings Team,
I feel like I'm missing something simple on this one, but it's a general problem that I encounter a lot.
For this question, I'm able to construct formulas that Smartsheet accepts, but the current version is long and more difficult to understand/manage from other users. This involves using IF, FUNCTIONS, and OR (for the different searched fields). I'm looking for a more elegant/simple solution.
Current Setup
Currently, the in-scope formulas are structured like this
=IF(OR(CONTAINS((search_for #1), (field range / reference #1)),
CONTAINS((search_for #2), (field range / reference #1)),
CONTAINS((search_for #3), (field range / reference #1))),
value_if_true, value_if_false)
Seen above, I'm using the same function (CONTAINS) and the same reference (#1) multiple times. It's wasteful and generates a lot of fluff, especially when you have multiple nested IF functions and a dozen search criteria.
Goal: It would be more efficient in this example, to have one function (Contains), one reference, and multiple search_for values, if possible.
Example Setup that doesn't work... (But if it did, it would solve my need)
=IF(CONTAINS(OR(search_for #1), (search_for #2), (search_for #3)), (field range / reference #1)),
value_if_true, value_if_false)
How can we make this^ work?
Thank you for your attention.
Tags:
- Sheets
- Formulas
0 · Share on FacebookShare on Twitter
Genevieve P. Employee Admin
05/13/21
Options
Hi @Michael W.
With the combination of these three specific functions (IF, OR, and CONTAINS) then the way you currently have it set up is the only way that I know of to have the proper syntax.
You can use @cell sometimes to replace a range within an OR function, but not if you're also using CONTAINS.
For example:
=COUNTIF(Range/Reference, OR(@cell = "Value 1", @cell = "Value 2", @cell = "Value 3")
But since you're looking to see if a range Contains a certain value then you would need to spell out each possibility within the OR function, as in your first set-up. Additionally, the way that IF functions are set up, this means that listing a range and putting a comma after it would break the logic statement, which is why I used a COUNTIFS.
From what I know your goal isn't possible, but if it is, this is the sort of puzzle that @Paul Newcome is a wizard at! It may also be helpful to see your actual formula so we can identify if there's another way of simplifying the structure.
Cheers,
Genevieve
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/14/21
Options
Thanks for the tag, @Genevieve P. Right off I don't see a way of simplifying unfortunately, but I am definitely curious now. I am going to have to play around when I get some free time to see if I can figure something out. I too have run into this frustration, but it was never enough for me to want to figure something different out.
I'll keep everyone posted on what I find over the weekend (I actually have this one off for a change haha).
1 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/14/21
Options
What is the reason for using the CONTAINS function?
1 · Share on FacebookShare on Twitter
Michael W. ✭✭✭✭
05/14/21
Options
Greetings Team. Thanks for your feedback so far.
- In this particular example, the formula is looking for specific text (e.g. 1, 2, or 3) in a multi-select column (e.g. 1-12) on the same row.
- Thus, I could use CONTAINS or HAS to get the function to perform as intended. HAS would have a slightly different formula structure.
- In both cases, I'm searching for information from a single reference cell-- not a long range.
- The IF is added to return calculations if the desired text is / is not present.
So the slightly more defined structure would look something like..
=IF(OR(CONTAINS("1", [Column Name]@row),CONTAINS("2", [Column Name]@row),CONTAINS("3", [Column Name]@row)),Date_Calculation_if_True, "")0 · Share on FacebookShare on Twitter
- In this particular example, the formula is looking for specific text (e.g. 1, 2, or 3) in a multi-select column (e.g. 1-12) on the same row.
Paul Newcome ✭✭✭✭✭✭
05/14/21
Options
Ok. Now let's take a look at it from the other side. How many options are there total? If there are only 4 options, then you could say that if it contains "4" then leave blank, otherwise "date_calculation".
=IF(CONTAINS("4", [Column Name]@row), "", date_calculation)
Could that be a potential solution, or do you have enough variables to make that approach just as bulky if not more?
0 · Share on FacebookShare on Twitter
Michael W. ✭✭✭✭
05/14/21 edited 05/14/21
Options
That's a fair question to ask and a good mindset to keep in mind-- to search for the null instead of the presence-of.
In this example/project, I'm looking for 4 different pieces of text within a multi-select of 20 different pieces of text.
--> Thus, this formula alternative is not a superior solution.
Thank you for your thoughts.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
05/14/21
Options
Ugh. Of course. Haha. I will fiddle with it a bit over the weekend to see if I can find a more efficient way to do this, and I will let you know if I have any additional questions.
0 · Share on FacebookShare on Twitter
Michael W. ✭✭✭✭
06/08/21
Options
Greetings @Paul Newcome. Did you have a chance to play around with this topic?
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
06/08/21
Options
I haven't been able to figure anything out just yet. I'll keep experimenting though.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
06/08/21
Options
Are you able to provide more details into your actual process?
You mentioned having 20 different options and a nested IF statement. Also incorporating this OR/CONTAINS leads me to believe that you want to have a specific output for each of the 20 different selections but that some selections could have the same output.
But... You say it is a multi-select, so if it contains "1" which has an output of "A" but also contains "9" which has an output of "C", how would you handle that?
There may be another way to approach this in an entirely different manner that we aren't thinking about.
1 · 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!
