-
Rankeq and Collect formula
I am having trouble with a Collect formula when I add another criteria. The Collect formula is embedded in a Rankeq formula. This formula (Rank column) works: =IFERROR(RANKEQ(DateNum@row, COLLECT(DateNum:DateNum, DateNum:DateNum, @cell <> ""), 0), "") When I add to the Collect portion of the formula (RankKL column) I get a…
-
Sheet Formula
Happy 2024 everyone, I hope your year is off to a great start! I'm back requesting help to get some formulas working. I am trying to use the countif formula to count a column in a sheet. I am trying to get it to count all the dates in the column, but I also want to use to the formula to count all the rows with that same…
-
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…
-
Index Collect Formula with Specific Text
Hello, We have one sheet with all of our projects in a column with various other columns of data associated. I am hoping to determine the best formula to look at all project names on this sheet and return them all to a column on new sheet if they have "Cliff" in the project name. I have tried a few different ways and none…
-
Formula to display next/upcoming date from a list
Hello, I'm looking to have a formula to display the next/upcoming date in the list. The formula is on another sheet (Sheet = "Helper Calculations"), so I need to reference another sheet (the one in the table below - Sheet = "Holiday Dates") within the formula. On this sheet, I can confirm the Date column is a Date type. I…
-
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…
-
How copy data from one sheet to another with multiple filters
Hi. I'm trying to get the data from a huge source sheet (40.000+ rows) into a destination sheet that should collect just the rows that match 3 conditions. We are talking about students: SOURCE SHEET COLUMNS ID - Name - Surname - Status - Course A - Course B 12 Mark Smith Enrolled Yes No .... In the destination sheet I…
-
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)`…
-
INDEX/COLLECT Q: Running into issues pulling data from another sheet based on 2 criteria
I am attempting to pull attendee numbers from a master grid to a summary sheet. Our master grid has upwards of 30 columns and is used as a central repository for event-related information across multiple years. I have been tasked with compiling a dashboard for our senior/executive leadership to showcase the total # of…
