-
The expected progress percentage does not match the completed percentage.
Hello, I'm trying to set up my spreadsheet in a way that I can have a column that calculates the expected percentage of an activity for the current day based on the start date and end date. The formula I am using for this column is: Expected percentage: =IF(TODAY() > [End Date]@row; 1; IF(TODAY() >= [Start Date]@row;…
-
Comparing dates
Hello everyone, I have three date columns with individual dates. In a fourth column, I want to display the next upcoming date which is not in the past. In Excel, I would have used something like this: =MIN(IF(A1:C1>=TODAY(),A1:C1)) I'm not sure how to achieve this in Smartsheet, especially when it comes to comparing…
-
Auto populate a contact cell but still with overwrite capabilities
Hello, I am trying to "suggest" a contact in a contact list column but can't find a way to automatically populate a cell while still having the ability to overwrite it later if that suggestion is not correct. So I've got a Program column, a Suggested column, and a Assignee column. Program is a dropdown and Suggested…
-
Complex Smartsheet Formula Help - Duplicate Entry Searching
I'm working on a sheet that logs employee FTE changes across various grants. Over time, one employee might accumulate several changes across different grants. The sheet columns are as follows: Employee Name, Grant, Name, Previous FTE, New FTE, Approval Status, and Record Status. If two or more records are referencing the…
-
COUNTIFS with NOT Logic
Hello everyone, I have a sheet that summarizes the data across our portfolio. There are three columns I want to use in my COUNTIFS function: Core Assignment, SLT Flag, Status. I want to count all the projects that equal the core name, have the flagged checked, and status is NOT in Completed, Cancelled, or On Hold. The…
-
Potential IF/AND with multiple criteria (each listed multiple times)
Okay, let's start out by saying I am so stuck I have no clue on how to create this formula! LOL Okay the formulas will be added to the PA - CAPA List sheet. The Training Report is the data source. This is just a small sample of my data (there are about 25K rows). In the sample on the Training Report you can see the names…
-
Formula to Calculate assets from Week Commencing Date from previous week.
I’m trying to work out a formula to calculate how many assets were created in the previous week from another sheet. I’m using a Week Commencing date which is the first Monday of each week and entries are being dividing into w/c based on when they were submitted to Smartsheet. How can I return the number of assets from the…
-
How to use Vlookup or Index/Match to automatically mark a task complete in destination row
Hi! I have a task tracker in which things are marked "Complete" from a dropdown and then checked off. The checkbox has conditional formatting that puts a slash through the entire row. Some of these tasks are delineated as "projects", but I've created an automation to copy a row if it is labeled a project, so that anything…
-
Anyone know why this IFERROR formula isn't working?
As you can see I want the formula to return a blank if there is an error but it's still returning "#BLOCKED"
-
Update formula so it ignores empty rows
Hello. I am using the below formula to mark whether a task is overdue or not, and then highlight using conditional formatting those overdue tasks in red: =IF(AND(Finish@row < TODAY(), Status@row <> "Complete"), "true", "false") I would like to update the formula to ignore blank rows. How may I accomplish this? Also, open…
