-
Trying to count the occurrence of the first letter of a string in a column...
I have a "Serial" column which can have two types of serial formats. One type starts with "S" as in "S1234" and the other is "R" as in "R1234". I need to count the number of serial numbers starting with "R" so I can then count the number of them past-due in another column. I started with =countif(Serial:Serial, "R****"),…
-
Alerts - AND not OR?
Is there a way to set up an alert with multiple changes that it's an AND and not an OR? I have a workflow that really needs to be - A new row is added, Status is Approved AND the path type is XYZ. Currently, the person being notified when anything is being approved and she only needs to be notified if the path type is a…
-
Understand % Complete for a set of tasks
How does Smartsheet determine % Complete for a subset of tasks? Task 1 has 4 tasks: 0%, 100%, 20%, 0% complete. Smartsheet has Task 1 at 34% complete Task 2 has 4 tasks: 0%, 100%, 0%, 0% complete. Smartsheet has Task 2 at 33% complete Thank you.
-
How to calculate time
Hello Everyone, I hope you are well and safe, I am working on one project in which I need to check if the time is greater than 24 hrs. For Example, if the Status is changed to In Progress the created time would be 11/17/22 4:08 PM now I want to check if it is more than 24 hrs means 11/18/22 4:08 PM and still, the status is…
-
Whats wrong with this formula?
I'm using this in a metric sheet to count the term in a sheet that has multiple entries per row from a drop down list. It currently produces the error message #INVALID OPERATION. =COUNTIF({PH Roadmap Intake sheet Range 1}, CONTAINS([Primary Column]@row)) I was attempting to copy the one @Paul Newcome provided on 9/8/21 but…
-
Combine if ISBLANK in an existing formula that pulls percent complete based on duration
I have the formula that pull the percentage complete based on the duration between 2 dates =IF([Start Date]@row <= TODAY(), NETWORKDAYS([Start Date]@row, MIN(TODAY(), [End Date]@row)) / Duration@row, 0) What I'm trying to do is add the formula below to make a combined formula. Which should make the number zero if there is…
-
No match for parent values only
I am using INDEX(MATCH to pull values from another sheet, it works for all children values but is returning a #NO MATCH error for the parent rows (see Goal 1 and Goal 2 below). The sheet it is indexing from clearly has the goal names, they are just parents so I assume that is the issue but I can't find a workaround. This…
-
Formula Help
I have created a sheet with a column with a drop-down and two options with different costs associated with each one. I am trying to create a formula so that when one option is chosen, the charge is added to a column. For example, if someone chooses Exam Extension, the Cost is $100, so in the column Total Cost, I'd like…
-
Join/Collect Multiple Columns to One Cell WITH Column Name
I'm trying to combine data from multiple columns across a row into one cell in that row that I can zap to another program. I'd like for the information from each column to be preceded by the column name. (Example "Column Name: Column 1 Data, Column 2 Name: Column 2 Name: Column 2 Data, etc.) I've figured out how to collect…
-
Get words based off a conditioning format colored cell
I have a status column that is already created to generate a color based on what is presenting in certain columns. I have the colors red for denied, green for approved, and yellow for pending. I want to have another column that is the status words of approved, pending, or denied based on the conditioning formatting colors.…
