-
INDEX MATCH (Multiple Criteria) vs INDEX COLLECT
Hello Smartsheet Community, I have a metric sheet set up where I am pulling from a much larger budget sheet - on my metric sheet, I want to pull in the market and due dates based on the project description. Nearly all projects have a unique description and my INDEX MATCH function works without issue; however, there are a…
-
Count Collect formula crashing large sheet - Scheduling conflict identification
Hello, I am running a Count(Collect) on a sheet with 8000 records. The formula calculates for a small number of records but if I make it a column formula or copy it to every cell then the sheet crashes. Does anyone know how to count thousands of rows that meet certain conditions without crashing the sheet? Purpose of…
-
Join+Collect by rows
Hello, I am trying to create a smartsheet formula to aggregate summery information from an amendment sheet onto a master sheet. However, when I attempted to use join() +collect() my cell returned all date finalized collected then all #s collected and then all types collected. I want to return data in ROWS not COLUMNS. In…
-
How do I list projects from a specific department across multiple cells?
I have a Smartsheet where one sheet contains a list of projects categorized by departments. Each department has multiple projects listed under it. I need to extract and display all projects from a specific department in another sheet, with each project listed in its own cell vertically down the column. I've tried using…
-
Need help with COUNTIFS and Count(Collect) formulas
Hi Formula Gurus, This should be straight forward but I keep getting 0 or 1. I'm looking for a count of projects do not have a Project Category of "test" and they have Year Started is 2023 (or yr 2024 in example 2) and Quarter Started is Q3 (or Q2 in example 2). The first formula below results in 0 (zero). This sheet is…
-
Adding Checkbox Not Checked to Working Formula
I have a working formula that I'd like to add a condition to, if possible. Here is the working formula: =IF(ISERROR(MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31))))), "", MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi -…
-
Count & Collect giving Error
I have multiple Metrics sheets where I use essentially the same formula to collect information. For some reason I am not seeing, I am getting an error with this one. =COUNT(COLLECT({Row ID}, {Category}, $Label@row, {Wave}, W1B$1)) The intention is to collect the number of Tickets assigned to each category during Hypercare…
-
How to get Join Collect formula to work
I have a sheet (picture attached) where I am trying to show all of the tasks dependent upon a certain task from being done. I want to sue a join Collect formula to list out all of the tasks referenced in the the predecessor column that match the row #. For example, in the picture, Row 70 would show the task "eComm Mezz…
-
Join, Collect functions
Hi All I am trying to retrieve results from several columns on a seperate sheet into 1 cell on another sheet. This formula works - =IF(INDEX({SUS Badge}, MATCH([Full Name]@row, {SM Name}, 0)) = "Not achieved", "SUS Core", " ") But as soon as I add more columns - =JOIN(
IF(INDEX({SUS Badge}, MATCH([Full Name]@row, {SM…
-
calculate maximum date
Hi everyone, I need your help to calculate the maximun data for each resource, starting from a sheet where the activities are recorded. I have a sheet where I write all tasks for each resource: I need to have the maximum date present in the "task" sheet for each resource present in the "resources" sheet Can you please help…