-
Change "end date" based on status changes
When an end date is used in a dependency setting, you can't use automation or formulas to change it. However, this is when it is critical (pun fully intended)! When someone marks an item as Completed status, it would be helpful if the sheet could update the End Date column to the date it marked completed. For dependencies…
-
Circular Reference Error
I am trying to create a formula showing the utilisation of my Project Managers based on hours against projects and PTO vs. their full time contract hours. I am using the following formula for Project Manager 1: =SUMIF(Person:Person, "PM1", Aug:Aug) / 140 When I apply the same formula for PM 2 (substituting PM1 name for…
-
End of Month Formula
I am using this formula to look at the created date of a submittal and then generate the end of the month date based on when it was created: =DATE(YEAR(Created@row ), MONTH(Created@row ) + 1, 1) - 1 But if something is created on the last day of the month, it puts the end of the month date for the next month. So, for…
-
I'm trying to create a tracking system using two sheets.
One sheet is the Gift Card Quantities has the number of existing gift cards in each dollar amount and the other is a tracker that allows the user to enter the gift card used, dollar amount, quantity of gift cards. What I want to do is if a $50 dollar gift card is used, then that number is subtracted from the existing…
-
How do I find the closest matching row that is above my searching row?
I am trying to find the closest matching row that is above my searching row. If Smartsheet does not allow searching backwards (above the current row) is there a way to search downwards, where the closest matching criteria to the searching row is displayed like the formula below is attempting to do? The following provides…
-
Need help with sheet reference formula to grab assigned user for a specific day.
I am working on some cross sheet data and sharing and would like to list in a cell of a sheet the user [Technician] assigned to a space [Bldg-Room] on Thursday from another sheet. There is a column for each day of the week on the sheet being referenced. [Thursday] I can use this formula within the current sheet but I seem…
-
How to Add and Equate for an Empty Field?
We have an intake sheet that we just updated to include a new field that will only have data input on a rare occasion. I need to figure out how to update my formula to take into account that input as we are measuring as a pass/fail system but I need it not to affect when it's empty. Here's the current formula:…
-
number of weeks between two dates
Please can you help find a formula to work out the number of weeks between two dates I want to know if there is a 5 week gap or not between date now request and proposed works columns the 5 week mark always starts on a monday e.g. if date requset was 30th July 25 then the five week mark would start on 4th august 25 - if…
-
RAID Formula for Dashboard
Does anyone have a RAID log formula they can share to calculate the overall and individual project health of a project based on the amount and priority level of Risks and Issues? Currently, I have formulas that evaluate the status of issues and risks in our RAID log which return color codes (Red, Yellow, Green) in a…
-
When cell populates, enter date
I have an automation set up for my Paid column. When a box is checked, then the automation runs and enters Paid into the Paid column. I would like to record the date the Paid column is populated in the Approved Date column. Is that possible?