-
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…
-
Combining selected cells into one cell ignoring blanks?
Hello all, I am trying to combine cell into one cell and I have found a couple other community posts covering this issue. The only problem is that their solution only applies if you are trying to collect the entire row, I need specific row. Originally, I did this just by using JOIN and then selecting each cell, separated…
-
Need help removing blanks from a join collect formula
Hi, I'm trying to use a join collect formula that referencing a column in a helper sheet. =JOIN(COLLECT({Column I Want Data}, {Reference Number}, [Reference Number]@row), ",") It's pulling everything from {Column I Want Data}, that matches the [Reference Number] with {Reference Number} on the helper sheet and any cell that…
-
COLLECTMOD() formula
=COLLECTMOD([Return Range],"Modify Return Range",[Range 1],"Modify Range 1", "Range 1 Criteria", ...) Leave blank if there is no modification to the range.