-
Date Modified - Copy of column creates different date
Hello, I am working to create an automation off of the date modified field. This is to alert users if they have not updated a row in more than 4 days. Since the date modified field is not recognized as a date field I created a copy of the column using the basic formula: =[Last Modfied]1 My results are not what I would…
-
formula that checks if a project happened during the current month
I'm trying to create a formula that checks if a project happened during the current month. Example: Project Start: 09/09/19 | Project Finish: 12/13/19 Here is what I have that kind of works: =IF(AND(TODAY() >= Start@row, TODAY() <= Finish@row), 1, 0) However, this formula does not work if the project happened during the…
-
Nested If with Multiple Check Box to Status Type
I was hoping I could get some help here. So I've decided to create a status for each of the tasks my employee is working on. Categories include: Done On Deck In Progress Not Started There is a checkbox column for each category. IF([Done]@row=1,"Done" IF([On Deck]@row=1, "On Deck" IF([In Progress]@row=1, "In Progress"…
-
Automating RYG balls to change colors
So i have parent cells and dependencies. The dependencies have color balls (red, yellow, green, grey). Here is what I want: If one of the dependencies has a red color ball, I want the parent ball color to be red. If all dependencies have a green ball, I want the ball for the parent to be green If all dependencies have a…
-
Lookup Table Question
I'm trying to figure out a way to apply my vlookup or index/match combo as a dynamic formula across my sheet. I have a set cell that I can use as the beginning of my array of cells to reference but I want the formula to update the final cell based upon the row I'm at minus 1. There may be a much better way of handling this…
-
SumIf with Date Range
I created a project management financial sheet (client / projects / deliverables & invoicing). I am trying to sum the value of invoices between a date range (screen grab). This is what I thought would work: =SUMIF(DTI:DTI, AND(@cell >= DATE(2019, 10, 1), @cell <= DATE(2019, 10, 31), InVAT:InVAT)) and it returns "0"? The…
-
IFERROR Formula for Dividing By 0
Happy Friday All: I am trying to replace IF Formula with IFERROR so I do not receive #DIVIDE BY ZERRO Error. However, I receive Incorrect when I use the IFERROR. Can anyone help? Existing Formula: =IF(AND(TODAY() >= [Start Date (Planned)]18, TODAY() <= [Finish Date (Planned)]18), ROUND(NETWORKDAYS([Start Date (Planned)]18,…
-
Making a decision tree in smartsheet?
Is it possible to make a decision tree type project in smartsheet? For example, I need to decide between options A, B, and C that have different cost implications and dependencies, but also need to plan for the potential staff and cost allocation. As an analogy, let's say a piece of equipment is breaking down, and I'm…
-
Flagging duplicates from 2 separate columns
Greetings - I'm having an issue trying to flag duplicates from 2 separate columns in the same sheet. I have been able to flag duplicates in a single stand-alone column but trying to using the same formula with the "COUNTIFS" function is leaving the flagged cell blank (as seen in row 2 of the screenshot attached). Here is…
-
Dynamic reporting across multiple sheets
My organization recently moved from Google sheets to Smartsheet. We were tracking the number of jobs completed by each team member in the last week. We had multiple groups of jobs that would be uploaded as a new tab on the sheet. The reporting we had set up was able to search the new tabs for data to include in the report.…