-
Difference between the first entry and last entry
I want to find the difference between the first entry and the last entry for the numbers in the "Current" column. I am using this formula: =INDEX([Current]:[Current]), MATCH(MAX([Date]:[Date]), [Date]:[Date], 0)) - INDEX([Current]:[Current]), MATCH(MIN([Date]:[Date]), [Date]:[Date], 0))
-
I want to send automated email to "assigned to", from a sheet, but only one reminder email vs mutlip
I have title, "assigned to" column, status(task 1), due date (task 1) columns, status(task 2), due date(task 2)…it goes for at least 10 tasks. The conditions to trigger the emails are, trigger 2 days before due date(when status is not equal to completed) and leave weekends trigger on due date(when status is not equal to…
-
Can you collate Ancestors based on another column condition?
Hi all, looking for some formula help. I would like to collate the names of my ancestors for a task in my "Stage" column, but only if the Hierarchy level of that parent or grandparent is >= 3. I have tried utilizing the Join(Ancestors(Task@row), " - ") and this returns all of the parent tasks. When I try to utilize an if…
-
Index Match with a multi-select dropdown
I am trying to write a formula that looks at a multi-select column to pull email addresses from a reference sheet. With my current formula it only works when there is one value in the multi-select column. =JOIN(COLLECT({Reference Sheet | Contact Q2}, {Reference Sheet | BID}, HAS(@cell , [Sites to be included]@row )), "/")…
-
Formula to check a box if a PARTIAL string is present in a specific cell in a descendant row.
Hey Folks! I have a working formula to check a box in a parent row if a condition is met in a descendent row. In this case, if the row "Type" is "Action Item". This is working and the formula is as follows: =IF(COUNTIFS(DESCENDANTS(Type@row ), "Action Item") > 0, 1, 0) This works well because the cell type is a drop down…
-
Decreasing a set number when a name appears
Hello! I am trying to create a formula for assignments where I can set a number (3), and when a name appears in a sheet, that number decreases for each time the name appears. I also currently have a formula where I have the number of times a name appears / 5 (we have 5 days of the week displayed each week so while the…
-
Can Someone Share a Working WBS Formula?
I'm having issues with WBS formlas. I have 3 columns: RowID Level, and WBS I pulled formulas from a Smartsheet template online (from a forum) but it's not working properly. The first row is the Project Title The second row starts Phase 0 - Project Initiation — it starts with 1, Numbering structure is 1, 1.1, 1.2, etc.…
-
AI (Generate Formulas) History
Allow AI to show history of what was asked and the results for future reference.
-
How to automatically add NA to a date column in 1 case, which is updated manually in other cases?
Hi, So, I have a date column, say Date A. I have another column say 'Condition A'. When 'Condition A' column consists of a value 'ABC', then Date A column should auto populate with 'NA'. In other conditions when Condition A column has 'XYZ' or @'PQR', Date A column needs to be filled manually by the smartsheet user. Now,…
-
Distributing Savings Across Months Based on Term Date and Fiscal Year
Hello My Super Intelligent Smartsheet Community! I have attempted to solve my problem by breaking it up into pieces and I'm stuck. I think I've been trying to solve this for too long and my creative juices are fried. I have Service Providers we have contracts with. Each contract has a Contract Start and End Date and our…