-
Auto-Number Based on Parent Child Relationship + Move Rows in Work Breakdown Structure
Hello Community, My company is trying to use a sheet to track all open eMarketing efforts each with a series of sub-tasks. I found a great solution within this community and is based on this: https://www.smartsheet.com/marketplace/templates/work-breakdown-structure-wbs , where I can have an ID for any added new row with a…
-
Why is a CONTAINS() workaround working in a JOIN(COLLECT()) function?
Hi all, I stumbled upon a solution for a complex request I received today, but I found it by accident and I don't fully understand why it is working. The code is: =JOIN(COLLECT(B:B, A:A, CONTAINS(@cell, C@row)), CHAR(10)) I want the formula to output an entry in (B) that corresponds with (A) based on the numeric code…
-
Help with "AND" formula
I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an "Invalid Data type". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, "2019", {Product},…
-
"Review status" based on multiple checkboxes
Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semi-annually, annually, bi-weekly, daily, quarterly,…
-
Function like UNIQUE in Excel?
Does Smartsheet have a function similar to the UNIQUE function available in Excel? The function allows you to select a range, and then provides a unique list from that list. In Excel Combining: SORT, UNIQUE & FILTER will provide a unique list excluding blanks that is alphabetized. I am constantly using that function within…
-
IF statement with multiple conditions
Hello! I am currently working on an IF statement with multiple criteria and I'm having no luck. Basically, I would like the formula to articulate that if a specific manager is selected, then column 6 will multiply the data submitted in column 5 by a specific number, presenting me with its value. If Column 1 is one of the…
-
Date@row not working properly
Hello, We had a consultant create formulas and sheets for us to combine like-information in multiple sheets. However, one formula that she set up is not grabbing the correct information. We no longer have access to this consultant. In the first picture below you'll see the formula = Date@row + "" in row 13. The same…
-
Blank cells, iferror?
Hello! i think i need an iferror to ignore the blank cells but i havent fully wrapped my head around those yet :) three things in this 90+ Total current formula is =SUM([90+]@row:[180+]@row) but i want to ignore the blank lines same for % over 90 current formula is =([90+ Total]@row / Total@row) adding the % over 90 as a…
-
Either checkbox column to contribute to % complete
I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to…
-
I am trying to return where a contract is in the review process, DOJ review may or may not have date
=IF(Started@row > [To OC&P]@row, "HSD Contracts", IF([To OC&P]@row > DOJ@row, "OC&P", IF(OR(DOJ@row > [To Contractor]@row, "DOJ", IF(DOJ@row = 0, IF([To Contractor]@row > [To OC&P]@row, "Out for Signature"), IF([To Contractor]@row > [HSD Signed]@row, "Out for Signature", ""))))))