-
How can I search another sheet for a name match within a certain date range?
I'm struggling to create a formula that will not only look for a name match from one sheet to another, but it will only return a match if the date on the row was within the past 6 months. For finding a name match between sheets, I've been utilizing the following formula: =IF(CONTAINS(Contractor@row, {Change and Offboarding…
-
VLOOKUP to Return Two Users
Hi! I need a formula that looks at two columns and returns an email address based on vlookup if one of the two columns is blank and two email addresses (again based on vlookup) if neither column is blank. The formula works well when one of the columns is blank and only one email address is returned. The problem is when two…
-
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…