-
Help to Simplify Logic
I'm creating a smartsheet with several columns and created a few formulas that I believe could be simplified to achieve the same result, but I can't seem to think of an easier way. Can you please try to help me out? HEALTH COLUMN =IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow",…
-
How to create formula to count over the past seven days
Hello, I'm trying to count the number of projects delivered in the past seven days. This is what I have so far: =COUNTIFS(Status:Status; "Delivered";Delivery Date:Delivery Date; >=TODAY(-7)) I'm not sure what's going wrong or where to insert the formula in the sheet (will any cell do?). Thanks, Emily
-
formula use in contact column
Hello all, Now that formulas can be used in the contact column - fantastic! - what is the syntax to reference a particular contact value? - I am trying to set up an IF <columnA value> ="blah" then assign contact X. I have tried using various strings for the true/false cases but am getting a formula reference error. Thanks,…
-
Sum all Rows
My sheet has 3 columns (Team, Activity, Amount). I have a summary section at the top of my sheet where I want to use a SUMIF function to total up the amount for each Team. Generally, I can get the SUMIF function to work just fine. But, I run into an issue when adding new rows. The function looks like this:…
-
IF formula with multiple criteria
I am attempting to write a formula based on 2 separate criteria, though I feel like I need some type of "BUT" statement and could use some help. I want a picklist column to read YES or NO depending on the following: IF Ins Status = Current OR N/A AND Agreement EXE is checked THEN Approved for Work = YES, BUT, IF Agreement…
-
Formula for percentage complete and duration remaining
I am trying to develop a formula to flag tasks that are less than 50% complete but more than 50% of the duration has passed. I'm not sure if my math is correct. But aside from that, can Smartsheet perform functions like the one I am proposing below? Currently, it returns an unparseable error. If I get this formula to work,…
-
Changing Drop Down Value Based On Current Date?
I am trying to change the drop down value on my Health column based on the current date. Is this possible? ie. If the start date value is in the past, the health column changes to "At Risk" which is one of the drop down choices.
-
Count only if Parent
I have a [RowID] column that is populated by this formula =IF(ANCESTORS@row = 0, COUNT($ANCESTORS$1:$ANCESTORS@row, "")) It returns sequential numbers but, I need it to only count if Ancestor = 0 Secondly, I need the result fill format to look like this 0000 (like an auto number) 0001 0002... 0100 0101 0102.. 1000
-
Nested If Formula
Hello, I am trying to create a nested IF formula based on the criteria below. Below is also a screenshot of what I am trying to do: =IF([% Complete]1=0,"Not started" =IF([% Complete]1>=0 and <=1, "On track" <code class="CodeInline">=IF([%Complete)]1=1,"Completed" =IF([%Complete)]1<=1 and Due Date is past,…
-
Formula to return parent row for current week
Hi community, I'm attempting to find a formula that will return the parent row where the end date is equal to the current week. I have created a helper column that calculates the current week number. Now the question is what to do with it to get the result I want. Any ideas on formulas to use? I'm leaning toward…