-
Cross sheet functioning column formulas working perfectly...except for two rows?
Problem Summary: I have a series of formulas and cross sheet references which grab display data that meet certain conditions. They are working consistently across usage…except for two rows? For data sensitivity, I'll only be including the date fields out of the display data, but the same error applies to all final display…
-
Help with index/collect (or maybe something else?)
Hi, I am struggling with index collect. I'm wondering if what I'm trying to do may not be possible with Smartsheet but I'm not giving up until I ask you wonderful people! I have a large Smartsheet with many columns and I'm maxed out at 381 columns, my license is only to 400 columns so while I could probably figure this out…
-
Help With Index-Collect and Cross Sheet Referencing
Hi all, I am attempting to return a Contractor's name from a source sheet using multiple criteria (if they are subcontracting and what their scope of work is). The issue I am running into is the unique ID I use to connect the sheets (The PCM Number) has duplicate values when there are subcontractors (multiple contractors…
-
Is this a bug and if so, how do I mitigate it?
I have a formula to autopopulate a drop-down list field to show one or more items, grouping together a number of related rows in another table. =JOIN(INDEX(DISTINCT(COLLECT({EcoTech Activities Managers}, {Activities PIDs}, =ID@row)), 0), CHAR(10)) Basically collect the names of managers from another table where the key…
-
JOIN(COLLECT()), Return Multiple Values/Contacts
Hi there! I'm trying to write a Join Collect Formula that will return the associated departments with a project based on the Owners noted on the project. The Departments column is a multi-select drop-down, and the Owners column is a Contact List contact. My reference sheet shows the Department and associated contact(s)…
-
IfError, Average, Collect, and Round Up to nearest whole formula
I have this formula =IFERROR(AVG(COLLECT([Time to Complete]:[Time to Complete], [1. Assigned to]:[1. Assigned to], "W - Water")), "") How can I make the result round up to the nearest whole number?
-
INDEX(COLLECT - #Invalid Value Error
Good afternoon everyone! Looking for some help with INDEX(Collect. I have a main sheet set up and also have created a helper sheet. Once the install date on the main sheet is changed, automation copies that row to the helper sheet. I want to see on my main sheet the previous install date as well. This I've figured out. I…
-
Help with Join+Collect Formula
I need to reference another sheet to join and collect the skills of employees. The yellow is my desired outcome, and I'm using the following formula. Formula: =JOIN(COLLECT({Detailed Resource Data Master Range Skills}, {Detailed Resource Data Master Range Emp ID}, [Employee ID@row), ",") I'm not getting any errors, the…
-
Join/Collect (Distinct?) Help with Combining Separate Line Items Based on Unique Values
So I'm having an issue (lack of understanding) on how to use join/collect to turn a sheet like this: Into one that combines Qty/Supplies into one line item based on data from multiple other columns. End result like this: I would like to get it so that Supplies/Qty get merged when Department, ID Number, Drop Off Location,…
-
Complex Lookup
I have a main table that, given the team(s) involved in a project, needs to look up the contact info for members of all of those teams. ([Teams Involved] is a multiselect dropdown, and [Members of Involved Teams] is a contact list where multiple contacts are allowed.) It is to get the list of team members from a lookup…