-
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…
-
Include Parent Row text in JOIN formula
Hello, first time posting, long time smartsheet users. I am trying to include the parent row text in my formula but not sure how. My Formula will only pull the text of the CHILDREN but not the parent. I need column "X" to be "BI, Engineering" =JOIN(DISTINCT(DESCENDANTS([Data Group]@row)), ", ")
-
IF/And/Join/Collect
Hi Smartsheets community, I am trying to create a summary field in a sheet that joins all the text from one column that meets these criteria: has a created date less than 20 days old has a label of "decision" in another column OR a star for priority in another column. I have a sheet used to track RAID entries, and…
-
IF JOIN-COLLECT is blank returns #INVALID COLUMN VALUE
Hello! I have written a formula to drawn data from another sheet, however, when I add "IF(ISBLANK(" to it, it breaks. I've tried doing this two different ways: `=IF(JOIN(COLLECT({HF23 Vendors Acc/Dec}, {HF23 Vendors Email}, [Email 1]@row, {HF23 Vendors Acc/Dec}, "Yes"), ", ") = "", false, true)`…