-
Task Health Indicator Formula (IF/AND/OR)
Got burnt out looking for my exact scenario in the forums, so looking for direct help. Trying to create a formula to automate my Health column KPIs based on task status and assigned dates. Status options: Not started, In Progress, Blocked, Complete Desired Status Colors Blue: Status = Complete Red: Status <> Complete AND…
-
Check Box Across Sheets
Hi, I have two sheets, one is an intake sheet and the other is a metrics sheet. Metrics Sheet Plan has an OR(ISDATE( Formula to raise flag is a date is entered Agents has an IFERROR(INDEX(DISTINCT formula to capture the agents from the Intake Sheet Intake Sheet I want to add a Formula that can Raise a Flag under the PLAN…
-
IF AND OR Formula *almost* works correctly but one part!
Hiya folks- all arguments work correctly except turning Health the color Yellow when Recommended Due Date is within 3 days of current date and status is not Complete. Here is what I need to accomplish: IF [Status] is"Complete" or "Cancelled/NA" then [Health] ="Blue" IF [Recommended DueDate] <= TODAY and [status] <>…
-
Multiple Sequences Need to Be Complete/NA to Provide a Complete Value
Writing formulas is not a strong suite of mine - I've tried a few routes (IF OR AND) but I'm either running into invalid formulas or formulas that are not netting the right results. I am trying to write a formula where I'm looking for sequence values to be either Complete or N/A and based on those sequences being one of…
-
If Formula help
Hello, I am trying to write an IF formula for my smartsheet that adds the letter A, B, C or D or E to a column pending on a dollar amount listed in another column, but I cannot seem to get the ranges to work right and everything is listed as either A or B. Current base formula: =IF([Total Assets]@row >= 1000000, "A",…
-
Overall Project Health Formula
Hiya folks, We want one field to show Overall Project Health. Ideally, we would figure out how to average the colors and/or tasks past due to return RYBG for the overall projects (about ~230) so that we can report it on a Dashboard. Any ideas on formula would be so appreciated. Circular reference error formula in OPH row:…
-
How do you make IF and IF(AND statements work together
I am trying to populate a cell based on multiple date criteria. I am currently trying to use the below formula but I am getting an unparseable error =IF([Date Submitted]@row > DATE(2024, 9, 30), 25, ""), IF(AND([Date Submitted]@row > DATE(2023, 9, 30), [Date Submitted]@row < DATE(2024, 10, 1)), 24, "")
-
Formula
Hi all, I am trying to combine the following two formulas, only one works at a time. =IF([Variance (Target End date)]@row = 0, "Green", IF([Variance (Target End date)]@row <= -4, "Red", IF(OR([Variance (Target End date)]@row = -1, [Variance (Target End date)]@row = -2, [Variance (Target End date)]@row = -3), "Yellow",…
-
Please could someone kindly help me with this INDEX/MATCH/IF /AND Formula
Hello There Smartsheet Community Please could you kindly point me in the right direction with my formula? Sheet 1 Contains the following sheet references "Status" = Jira Status "MMP" = MMP I need to update the value of "Validation NL" based upon the following criteria: If "MMP" in sheet 1 matches "Req ID" in sheet 2 and…
-
If statements with <>, blanks, and errors
Need assistance putting all my "if conditions" into a single formula if possible
