-
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…
-
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},…
-
Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
-
JOIN COLLECT - PARENT criterion
Hi there! I'm doing a cross sheet formula where I want to collect Task Names if the IP Submission Return of the child row (10/12/24 in this example) matches the Most Recent Update of the parent row. The Most Recent Update column utilizes the formula =MAX(DESCENDANTS([Actual IP Submission Date]@row), DESCENDANTS([IP…
-
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…
-
Using Join collect from referenced sheet while ignoring criterion
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP",{Prime Student Onboarding Worksheet Affiliate}@row))), CHAR(10)) trying to get this formula to work while ignoring cells in the rate total column that have a hyphen (this is formula…
