-
Joining text "if" a criteria is met in multiple rows / columns
Hi, I'm trying to do something a bit complicated and hoping for some assistance. I am trying to join text from specific columns IF another column has specific verbiage in it (Campaign 1). Below is an image of the rows and the column with that verbiage. Do you know what would be the appropriate formula for this? I want to…
-
Combine emails into a multiple-contact column / multi-contact column
Multiple-contact columns can be problematic to use in Smartsheet. Namely, they cannot be "put together" with a formula. There's many use cases where you need to build multiple contacts into a cell based on criteria/lookups, but there isn't a way to do that in native core Smartsheet. There is, however, a couple of methods…
-
How to do lookup for multi select dropdown cell to return results for all selected items in cell?
Hi I'm trying to do a lookup or index/match or whatever that can search all values in a dropdown list cell with multiple values and return multiple values. Sheet 1 screenshot (yellow highlight is where I want formula to be): Sheet 2 screenshot (contains table of lookup values): I'm trying formula from this question from…
-
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
-
Are You Able to Join Two Columns into a Data Shuttle Dynamic Dropdown?
Hi everyone, I'm hoping someone here can help me out with a challenge I'm facing in Smartsheet's Data Shuttle. I've been trying to use expressions to combine two columns into a single dynamic dropdown column in a target sheet, without changing the source sheet. For example, I want to combine Num and Name into a format like…
-
How do I join specific cells from multiple sheets?
The question seems simpler than the outcome I'm hoping for. I need to combine distinct project types from multiple columns on multiple sheets. I know I need to use Join, Collect, Distinct, and potentially others, but I'm struggling with making it work the way I want it to. This is what I have: This sheet will be duplicated…
-
JOIN is missing items when joining multi-select dropdown column
I have a dropdown column (multi-select) that lists participating districts. Each row is a different program that can have different districts or sometimes the districts repeat. I am trying to count how many unique districts participated. In a separate column (dropdown - multi-select), I am trying to use JOIN to pull all…
-
Removing blanks from Join Collect output
I have a sheet that is pulling in data for metrics from another sheet. Using Join(Collect() I get the output I want but, it does include the blank cells from the range. I would like to not have them included…. =JOIN(COLLECT({PECR Range 6}, {PECR 2}, Region1), SUBSTITUTE([Column6]1, "-", ""))
-
I can't get my Index Match Max formula to work- has error #UNPARSEABLE
Trying to find replica of excel's Xlookup formula. Here is my current formula that is not working: =INDEX({GP% TO PAY}, MATCH(MAX({HIGHEST TIER%})([% to GP Budget]@row, {HIGHEST TIER%}),0)) I am trying to pull in Employee's % to be paid on their bonus, based on the current sheet's % to GP Budget result. The helper sheet I…
-
Index Collect with an IF and a Join?
Good morning all I'm after help with a complex formula concept. FYI these are not live data sheets just moc ups. Data Base Sheet Metric Sheet Bellow is the formula im currently using to pull contracted orders across to a second Metric sheet. =IFERROR(INDEX(COLLECT({DATA BASE | Customer}, {DATA BASE | Start Date},…