-
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.…
-
Is there a way to maintain currency formatting when within a formula?
Hello all, I want to use document generator to make annual compensation letters. Leadership wants the compensation dollar amounts to be formatted within paragraphs, which makes a traditional fillable form a little challenging when I need to be super precise about being in line with the rest of the paragraph. They want one…
-
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…
-
Lookup on a Many to one sheet
Okay, this may be a bit tricky, so I would appreciate your help. I will be creating a new sheet, and accessing both of the sheets below. Let's call the screenshot at the top, SheetA, and the one at the bottom SheetB. Now, SheetA is a "one to one" with the TDLs listed in the column on the right, there is only one entry on…