-
Is there a way to simplify this formula?
I have 3 columns with dropdowns of 'Complete' 'In progress' and 'Not Started'. In a fourth column (Percentage of Task Complete) I have a formula that assigns a value to each of those text items and then does an average and converts it to a percentage. Here is the formula: =(SUM(IF([Step 1]@row = "complete", 100, IF([Step…
-
Per diem interest rounded to nearest cent doesn't translate to accumulated interest?
Happy Thursday!!! I've got a sheet that tracks loans to generate statements and payoffs. For the most part, it works GREAT. The "Holding Cost" (per diem) column is run by =[Loan amount]@row * interest rate / 365 Since the column is set for Text/Number & Currency, it automatically rounds to the nearest cent which is perfect…
-
IfError with Index and Match
Hi all, I am trying to troubleshoot a formula that has worked for me in the past and wondering if anyone can look it over. Typically this formula is used for a travel grid that denotes when someone is Work/Travel Out (X/T) or just Travel Out (To). Happy to share the sheet itself as well to look at further.…
-
Contract Managment set build assistance
Has anyone created a contract management set that links all variations/milestone requirement & payments/ reporting and review dates. The contract set in the solution centre does not suit my office size or way we work. I don't want the top-level source sheet to be bogged down with too many details so i need the underlying…
-
Help with REPLACE formula
Hello, I'm trying to implement some form of auto-fill functionality in my schedules using the REPLACE function. I've attached an image of what I'm trying to accomplish. In the example I'm trying to auto-replace the "BRAND" text in subtask 3 and 4 with the "NIKE" text in line 2. The idea being that I have a schedule that…
-
NETWORKDAYS with IF
Hi I have a NETWORKDAYS formula that will calculate work days between a begin and complete date, but often our projects are paused, so I need a formula that will subtract the net work days during the pause. I figured that out, as well: =IF([Completed Date]@row = "", "", NETWORKDAYS([Begin Date]@row, [Completed Date]@row) -…
-
Health status formulas and automation
Hi, is anybody able to help with automation formulas for the Health column? I'm very new to Smartsheet and struggling a bit! I'm trying to do the following: Green - Status='In Progress' and more than 1 week before end date, or status= 'Complete' Yellow - Status= 'In Progress' but end date 1 week away Red- Start date is…
-
Inconsistency with copy row automation
Hello! I am running into some issues when attempting to utilize a copy row automation. Sometimes the rows are copied to another sheet, and sometimes they're not. Here's a breakdown of my process... Sheet 1- approval workflow is automated upon form submission, records a date when approval is granted Sheet 2- utilizes a…
-
Generating values that can be referenced in a drop-down
Hi, I'm looking to create a formula and cell reference system that can be used to generate a value that can be referenced in another cell or ideally a form. Here's the workflow breakdown. The field fills out a form that creates a new line with an auto-generated "Request Number" If the form has a "Build" in the "Request…
-
Normalized Rank Formula
Hello - I am trying to recreate the formula below in my sheet. However, instead of using tabs in excel (e.g. 'Project Type Weight'!B:B), I am tagging the row and dragging the number of rows down (row1:row100). I am getting either an unparseable or invalid operation error. Can someone please help? 🤣 Excel Formula:…