-
Help with this Formula
I'm trying to seta formula for the health of the project's tasks based on certain conditions. This is the formula: =IF(OR(AND(Status@row <> "Complete", [End Date]@row < TODAY()), AND(Status@row = "In Progress", VALUE([% Complete]@row ) < 0.3, NETDAYS(TODAY(), [End Date]@row ) <= 30), AND(Status@row = "Not Started", [Start…
-
Planned % rollup formula giving higher % than % Complete
Hi Community, I’m trying to build a reliable Planned % column that rolls up consistently, so I can compare it against % Complete at the project and phase levels. Setup I created an Old Planned % column that calculates planned progress for each row based on Planned Start and Planned Finish dates: =IF(OR([Planned Start]@row…
-
Match Contact column list to Email Address
Has anyone found a practical solution to match a Contact column to an email address? It can't be done in DataMesh and while I've done some searching on the board and help files, I haven't found a method to do this that doesn't require a lot of manual intervention. The problem with Smartsheet Contacts is that people can…
-
Displaying %Complete Column on Metrics Sheet
Hello! My coworker and I think we are close to figuring out this formula on our Metrics Sheet but we keep receiving the "Incorrect Argument Set" message. Here's the formula in the screenshot: =INDEX(COLLECT({Per Complete}, {P2H Task}, $Reference@row , {Milestone}, 1)) {Per Complete} = the %Complete column in the source…
-
Writing Formula for Sheet Summary Field
Based on the screenshot below, I am trying to write a formula that will pull in the reason for change on the row where " most recent change" is checked. Thoughts on the best formula? I've tried: =IF([Most Recent Change]:[Most Recent Change] = 1, [Reason for Change]:[Reason for Change] , "") Is there something I can do with…
-
Automation change cell value no longer being translated to a $$ value?
Hi, I've noticed this issue in two of my smartsheet processes. I have an automation set up as follows: "When rows are added are changed and [Column A] changes to [Any Value], change cell value in [Column B] to "60" " I then set column B to be $ currency format. Up until recently, "60" changed to "$60.00" without issue but…
-
INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…
-
Formula for impact score x likelihood
Hi i currently have the below formula but need help amending this =IF([Impact Score]@row = "", "", [Impact Score]@row * [Likelihood Score]@row I need to amend this for if column type is risk then impact score* likelihood score and if column type is issue then return impact score only Thanks
-
Split Multiple Selections In Single Form Into Individual Columns
If I have an option on a form for a multi-date selection field is there a formula that could help break out each date selected into its own column. So if it was a sign up form and they wanted to select 3 dates to work then each date they are working could end in its own column. Then I could set a reminder automation based…
-
DataMesh: What if the lookup value column is a formula?
I have a sheet to track travel, which includes a Start Location column and End Location column, and a third "Start - End" column which contains a formula that combines the first two columns. I've done this so that I can reference another sheet, which has the estimated cost from Location A to B, using DataMesh. However,…