-
How does COLLECT() with String Criterion Work?
I have a simple formula (I thought) that seems to broken. It seems to be centered around the existence of a String in the Criterion Parameter of COLLECT(). Formula: =IF(MIN(COLLECT(Date:Date, [Work Type]:[Work Type], "Mandatory"))=Date1, Hours1-6, Hours1) Result: #UNPARSEABLE Is it not possible to have a string Criterion…
-
Calculate progress towards a goal
I'm looking at two things - one to highlight when a goal is not on target to complete by a date. The second is note a specific amount in a cell on a certain date. To clarify on the first - I need a formula that will tell me if the number in the Target AUA cell is not going to be meet by the date next to the 90 days cell…
-
What's wrong with this formula?
It returns only Complete or Not Started. =IF([% Complete]@row = "1", "Complete", IF([% Complete]@row = "75", "End in Sight", IF([% Complete]@row = "50", "Work in Progress", IF([% Complete]@row = "25", "Just Started", "Not Started"))))
-
VLOOKUP returning #INVALID REF
I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it. =VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false) Below is the excel sheet formula that does work but does not flow over to the smartsheet. =VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE) If you look at the excel…
-
Formula - Adding together certain assigned point values
Hello, I have created the below formula that works up until the bolded part. I am trying to add this to the below formula: If Less than 100 it gets zero points. If between 100 & 499.9 it gets 1 point If between 500 & 999.9 it gets 2 points If greater than 999.9 (or 1,000 or more) it gets 3 points. I am not sure how to add…
-
IF > TODAY formula not working
I've been trying for hours to get this formula right and I think I've tried every combination. I have a date field. It can either Have a date Be Blank Have TBD Have N/A If it has a date AND the date greater than today OR if blank OR if it's TBD = not check If N/A OR date is passed OR it's blank = checked This is what I…
-
Parent Health Status of Children Rows Based On If Certain Criteria Is Met
Hello, I have a Gender column with these options: Female, Male, Transgender, Non-binary/non-conforming, Prefer not to respond I need the parent row to reflect a status of RED ball if there is no 'Female' represented in any of the children. I currently have this formula, but feel I'm missing significant COUNTIF references:…
-
How do I set an automation without a trigger or is there a workaround to create a trigger
Hello! I am needing to set an automation to lock and unlock rows. I have the automation set to lock the row when it is changed from “open” to any other value. I need a time based function that will allow the cell to unlock a certain time period after it is locked by the first automation. Essentially I want to ensure that…
-
Is there a formula in smartsheet that would create a 2 way link between cells?
Hello, I have multiple sheets that I would like to link all into one Master Sheet. I want the cells to have a two linking methods between each other. If a cell changes in the Master Sheet, I want it to reflect to the other sheets and if the other sheet decides to change the cells, it would reflect back to the Master Sheet.…
-
Needing Help with an IF TODAY formula
After reading countless articles and trying countless combos, I still can't get this formula to work. I am trying to give a 30 day notice to an expiration day with a status column. =IF([TIPS Expiration]-TODAY()>30,"Green", IF[TIPS Expiration]-TODAY()<30,"Yellow"))