-
The smallest date with a specific status in the child cell
Hello community, I hope this message finds you well. I need a formula to classify as TRUE if my row has a "Not Started" status and presents the smallest start date within the cells, which in the example figure, are filled in white. However, I couldn't come up with an efficient result. Any tips or help to build this formula…
-
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…
-
Can I link 2 sheets with a formula that feeds many date columns?
Dear All, would you please advise if this is possible? if yes, would that be a combination of JOIN/ COLLECT/ IF / AND? If no, is there a way around it? I need the dates in these 10 columns: to feed into these 2 date columns: The condition is that Discovery Start Date has to feed into Phase Start Date when the activity is…
-
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…
-
Requesting help reporting top three vendors for three locations based on a common review database
Our team is trying to build a tool to intake vendor reviews based on five averaged criteria to inform vendor selection on future projects. I've built the form, a review collection database, a review metrics helper sheet, a vendor masterlist and a preliminary report. I'd like to be able to consistently report the top three…
-
Max Collect in Sheet Summary
I'm trying to have the Sheet Summary list the employee name from the Repeat Category Winner column with the highest Repeat Count per Category, and I have 3 categories that I will use this for (Timesheet Not Submitted, Timesheet Not Approved and Time Off Not Approved). Formulas for those columns in the screenshots.
-
Help with translating text values into numeric values for graphs
Hello! I'm trying to create three (3) graphs with data we're keeping in a Sheet to go onto a Dashboard. There are 3 Teams that will each be one of the graphs, and then the data comes from a dropdown column and each option will be a bar on the graph. I'm having a hard time separating the data first by team and then second…
