-
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…
-
Seeking help merging data from two fields - one column is locked and another is a shared column
Hello, I have received a request from the business to streamline a process and minimize data entry, but I am not confident in how to cleanly execute. Here is the scenario: This is a compliance related activity. Submitter starts the process through a form. All the fields on the form are locked within the base sheet. One of…
-
FORMULA COLORS: Color Correct Part of Formula and Gray from error part forward. Don't Grey it all.
I LOVE that when you are writing a formula in Smartsheet, the formula adds colors to the references as you make successful references to cells. And how it goes "Grey" if there's a problem in the formula: However, It would be VERY VERY VERY VERY VERY helpful if you could keep the PROPERLY referenced cells in color, and ONLY…
-
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…