-
Join Collect - Return Multiple Values from a Multi-Select Column
Hi all, I have a change log where the "Assigned To" column has multiple assignees - I have another column where I would like to return the department(s) of the assignees, which is pulled from another sheet. However, when I use the following formula, it returns a blank result when I put two people in the "Assigned To"…
-
How to return multiple values from a sheet and paste them on multiple cells, based on criteria?
Hi, I have one sheet where there is a column with order numbers. There is also a shipping date column, which has a shipping date for each order number. The sheet itself is called Shipping. On another sheet, I want to return all order numbers of the next 3 weeks. I tried using the Index(Collect()) formula at first, but…
-
Contact Collect/Join to Contacts will not filter (Current user)
I have contacts in a Collect/ Join in an [Assignee All Row] Field At its core: JOIN(COLLECT([RowRange1]@row:[RowRange2]@row, NOT(ISBLANK(@cell))), ", ") As far as I can tell ", " Comma-space is the delimiter for multiple e-mails in a multi-select column. FAILS: Filters on the sheets won't recognize (CURRENT USER) in the…
-
Trouble with join/collect or index/match, with multiple columns and multiple results
Hello, I can't seem to get this right and after too many hours on this, I am escalating it to the formula masters of this great community once again. I am attempting to create a cell that will collect and join all the matched criteria from two different columns with the same criteria. There are multiple matches in both…
-
Collect Data and Join
Hi There I have a sheet which is populated by a form (Booking Form) where the user selects a date, and enters her/his details and the number of people, As these people select dates randomly, the Smartsheet list is not in chronological order. I have successfully created another sheet (Summary Sheet) where I use the date and…
-
Applying / Syncing Information from One to Another Using a Lookup or Join
All, First time posting here. I have two separate smartsheets (Smartsheet A and B). SmartSheet A contains our inventory. Smartsheet B consists of our Customer's Data such as which state they are in and GPS details. My personnel don't have access to SmartSheet A. They only have access to Smartsheet B. When my personnel…
-
How to JOIN multiple columns without delimiters
Here is my JOIN function used on Name, Start Time and Finish Time columns that works fine. However, I want to use it with IF empty show nothing (currently it shows the delimiters). =JOIN(Performer1 + JOIN(": " + JOIN(Start1:Finish1, " - "))) Any suggestion please?
-
Exclude from concatenated text if text equals "xxx"
I am trying to concatenate text from 10 cells that are not next to each other into 1 while excluding text that is equal to "Not Required". This is the formula I am using to concatenate the text. I have also tried the Join function without success. This is the result, which is correct, but as you can see it excluding "Not…
-
Help with JOIN(DISTINCT(COLLECT formula
I'm currently using this formula: JOIN(DISTINCT(COLLECT({Training}, {Identifier}, [Loc - Employee]@row)), CHAR(10)) in order to pull completed training records for a list of employees. The idea is that if a specific employee has multiple training records, I can use this formula to return these records to compare against…
-
How to match clinical trials to subjects based on eligibility criteria columns?
Hello, I have 2 sheets and I want to pull information from sheet to another sheet if certain criteria are met. The first sheet, "Slot List", is a list of clinical trials and the columns contain the study name and certain eligibility criteria patients need to meet to join the study. On the second sheet, "Trial Match", is a…
