-
Status Report based on certain conditions, i.e. dates, status etc
Hi Smartsheet community, I'm trying to come up with a status report to display information on what has been done in our projects between certain dates. I would really appreciate your expertise and help!!! For example: Project A Status Report for the period 04/Nov/19 - 17/Nov/19 What I want to show is: 1. Completed…
-
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…