-
Nested Index Collect to gather data from multiple sheets
Hi all, I am trying to create a sheet that pulls data from multiple others. I want to populate the person from Sheet 1 when the collection sheet column for client matches the Sheet 1 column for client. The basic formula from one sheet works: =IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row), 1,),…
-
Collect Multiple Row Data into a Single Cell Reference
Hi Smartsheet Community, I am attempting to create a meeting planning set of sheets, and I am struggling with the following. SITUATION: Provide a formula for referencing a stakeholder register sheet for the name of a meeting attendee, and display their PTO in the cell. Please note that there will be multiple attendees for…
-
Using AVG(COLLECT())
Good afternoon, I'm trying to do the equivalent of '"AVERAGEIFS". I read about using the collect formula, but I'm having an error. The formula I'm using is: =ABS(AVG(COLLECT({Master Sheet - 1960 - Net}, {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row))) I'm trying to calculate the…
-
How to Incorporate OR with a COLLECT Formula
I have the following working formula that I need to modify to look for either two people in the "Resi - Onboarding Specialist" column in addition to meeting the date range. The formula below is working for one application but the next application needs to also include calculating the median if the "Resi - Onboarding…
-
Return the max date looking at multiple criteria
Trying to add the most recent training completion date (column is date/non-restricted) using mulitple criteria, to include: email address, training type ("refresher", or "intensive") and status ("attended") to a designated column (date/non-restricted) on another sheet. The formulas below are pulling the same date into each…
-
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…