-
Sum cells from a reference sheet based on a range of dates
I need this formula to sum cells in a reference sheet based on dates. The reference sheet has a column with dates, and there could be multiple of the same date. I was able to get this formula to work on the top line in the screenshot, but the next line is adding the 2 week ending sums together. The planned bushels for week…
-
Assistance with IF, AND, OR statements
=IF([Due Date]@row > TODAY(), IF(AND(Status@row = "In Progress", Status@row = "New", Status@row = "Not Started"), "Yes", "No"))) The goal is to flag items for escalation based on the due date and status. If the due date is in the past and the status of the risk or decision is in progress, not started, or new then I need to…
-
Target Start/End Date
Ultimately, I need a good way to track if we are completing tasks on time based on our original estimates because we have users that estimate a task will take 5 days and when it takes 15 they just change the dates, and the project status still looks green, when is reality it is behind. I would like to have target start/end…
-
Performing an Audit - Best Practice
Hi - we are performing an audit and we need to take the data, and have the consumer validate that its accurate, and if not, identify the change required. What's the best way to tackle that? I was thinking a form but there could be lines and lines of data to validate; is it as simple as having them review the sheet for…
-
Need a formula that returns a date while factoring in dependencies
I’m looking to add a couple of columns into my project plan sheets. SmartSheet already has a [Start] & a [Finish] column built into its project template, but I’m looking to differentiate between [Plan Start] & [Actual Start] and [Plan Finish] & [Actual Finish]. I renamed [Start] & [Finish] to [Plan Start] & [Plan Finish].…
-
Row ID / Row link
I'm building simple asset tracking system with Smartsheet and would like to have the row links as QR-codes that would then be printed as asset tags. I have not been able to find a feasible solution for pulling the row links for each asset (row). Doing manual copy paste for >2000 rows is not very convenient. I have a column…
-
Formula to calculate a future date on a specific day (monthly and quarterly)
I have tasks that need to be completed monthly and quarterly. The due date of these tasks falls on specific days of the month, either the 5th, 14th, or 15th. I have a formula that calculates the Next Date based on a Due column and a Frequency of Submittals column. Next Date and Due are date columns. My basic formula to…
-
Have I stumped Smartsheet AI on calculating dates??
Hello, I have a sheet set up to track of the number of event reports received on team members (mock example below - my real sheet has goes up to Event10). The dates are entered manually but every time I enter the date, I want to calculate the number of event dates that fall within 3 years of the latest date I just entered.…
-
Counting tasks completed on time
I need a formula that says, if the "Complete?" box is checked, was the date in the Actual Completion column on (or before) the date in the Finish column.
-
Ways to avoid 25000000 formula cell reference limit
We have a sheet that we use for time tracking for some of our projects. We have about a dozen people supporting projects and they may log their time multiple times a day. I have a SUMIFS formula that adds the duration for each client project. Formula: =SUMIFS(Duration:Duration, [Client ID]:[Client ID], [Client ID]@row ) I…