-
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…
-
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?