-
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…
-
Formula to join cells from a contact list column while maintaining the contact list type for filter
An example would be to have a "Team Sheet" that has a column for "Team Dept." and another column for "Team members" that is a contact list type. In another sheet called the "Project Sheet," We'd have a column for the Project name, one for "Team Dept.," and then another for "Team Dept. Members." I'd like to use Team Dept to…
-
How can I collect and join IDs of other rows based on the own row ID?
I have the columns "Auto number", "I am following" and "Followed by". I manually maintain the "I am following" column by entering 1 or more IDs of other rows in blank-separated form. Now I would like to fill the "Followed by" column automatically by collecting all IDs of rows in which the respective row ID is mentioned in…
-
Add timestamp to Join/Collect Column Formula
Hi, I have a Join/Collect column formula that I'd like to add a timestamp to. Is this possible? If not, is there a workaround? Sheet1 contains a list of Accounts populated with answers from a questionnaire. An Account can have multi rows. Sheet2 consolidates these answers into a single row. The column formula in Column3 is…
-
Combine shifts (dates worked/clock in/clock out/ activity type for specified pay period & person)
Hello Smartsheet Community, Has anyone successfully used Join/Collect to combine data from multiple columns? I have a sheet with the time submission/shifts worked by employees and another sheet for combining the data. I need to collect all of the approved shifts for a specified pay period for each specified person, so each…
-
How to collect multiple Successor values?
Here's a link to my initial post which helped get me this far. RECAP: I use a combination of a Successor formula & Join/Collect in an attempt to report for my team the next tasks and assignee(s) in our waterfall workflow. I am able to successfully return the next assignee only when there is a single successor. I need to…
-
Help with collect formula with multiple AND arguments
Hi folks, I am currently using this formula, which works fine: =IFERROR((AVG(COLLECT({#Working Days to Kickoff with KP}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1))))), "") This gives me the average time we take to kick off our projects from the date we receive our projects from the…
