-
Ability to automatically update end date on a row, after row is created based on create date
We are currently using a Form to submit requests for changes and would like to use logic to update the end date of a row after the form is submitted and based on the create date plus X number of days OR after a status is updated, if the end date is blank update the row based on X number of days. Is this type of function or…
-
Formulas in Contact Columns
I have 3 columns on a sheet that holds new submissions via form. Based on the job# submitted, i pull in the assigned Dispatcher, Rep and Tech. I use these for alerts. However, only the Dispatcher column is showing correctly. The other two just pull in the name. See attached "Overview". I am using Index Match formulas in a…
-
Formula question
I would like a sheet summary column to count if a check box has been checked and then by todays's date minus 7 days. =COUNTIF([Show Stop]:[Show Stop], 1 < TODAY(-7)) I think Im close but it calculates zero. Any ideas on the formula? Thanks!!!! - JP
-
Bring back a value based on condition of another cell
I have a date column that based on it's value, it bring back a word. If the date is older than today, bring back the word 'red' If the date is within a 14 range of today's date, bring back the word 'yellow' If the date is greater than 14 days but less than 30 from today' bring back the word 'green' If the date is more than…
-
Need a way to fix row id, so they do not move
Hi, Little background, I am using below formula to get the row id in RowID column =COUNTIFS($Name$1:Name@row, OR(@cell = "", @cell <> "")) Now whenever I sort my sheet on the basis of date, the row ids also changes because the absolute reference changes. I am fine with sorting but I want the Row ID to always start with 1…
-
Invalid Data Type Error for Embedded AND statement within an SUMIF
I'm trying to sum project estimates based on department and type of project. Basically if the department is "Operations" and the type of project is "MC" then sum the estimates for those conditions? Also referencing another sheet. I receive an "Invalid Data Type" using the formula below. =SUMIF(AND({5 Year Capital Plan (LC…
-
Question about total time formula
Good Afternoon! I am successfully using the following formula to calculate total time from a start and end date and time: =((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start…
-
Count all of the Level 1 Parent rows in a SmartSheet
In the document's Summary Sheet, I want to count all of the Level 1 parent tasks in the Sheet itself. In the example above, the answer is 2.
-
RYG balls & Dates
Hi All, I am working on constructing a formula that will automate based on the dates (Start Date, Due Date and Complete Date) I can get the balls to automate using one color or another but not all four within the same formula. I keep getting an unparseable or incorrect argument error. The below formula actually works…
-
How can I get parent row roll up of child tasks health status
Hi - I am using health status which is determined by project status which is in turn determined by a planned vs actual task progress. I need the the health status to roll up to the parent row since I can't get an accurate weighted planned % complete in the parent row. I am using the following formula in child tasks health…