-
Can you collate Ancestors based on another column condition?
Hi all, looking for some formula help. I would like to collate the names of my ancestors for a task in my "Stage" column, but only if the Hierarchy level of that parent or grandparent is >= 3. I have tried utilizing the Join(Ancestors(Task@row), " - ") and this returns all of the parent tasks. When I try to utilize an if…
-
Using Collect to pull data from the same sheet
I usually use Index(collect to reference separate sheets to pull data, but I'm trying to apply the same logic for pulling column values from the same sheet. I tried this as an Index(collect formula =INDEX(COLLECT([QTY on Order]:[QTY on Order], [Ref PN 1]:[Ref PN 1], [Part Number]@row),1 & a collect formula on its own,…
-
Strange INVALID VALUE error
Hi everyone, I've encountered a problem in Smartsheet that I tried solving for a long time without success. I was creating metrics for a sheet, and I made a bunch of summary fields that contained my formulas. They all worked as intended when I created them. About a month passed without anyone touching them, I went back to…
-
INDEX COLLECT function returning #INCORRECT COLUMN VALUE
I have a sheet that contains the info of all of my employees which includes a column for their initials. The training department of my company has a sheet that I have view access to but not editor access. They track the qualification due date of all of my employees and I want to collect the due date on my employee info…
-
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?