-
New to Smartsheets and Formulas
Hello! I currently have this formula in my status column =IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress")) but I'd like to add a rule that if the finish date is < today, "Overdue". Can somebody help me complete the formula? Also, I'm looking for recommendations for training. I'm…
-
Can someone tell me whats wrong with this equation?
I'm looking to get Yellow when the due date is between 5-0 days, Red after one day late and otherwise green. =IF(AND(([Target End Date]@row) = TODAY(), ([Target End Date]@row - 5) >= TODAY()), "Yellow", IF(TODAY() > ([Target End Date]@row + 1), "Red", IF(TODAY() > ([Target End Date]@row - 6), "Green")))
-
How do you copy cells or groups of cells from another sheet based off of another cell?
How do you copy cells or groups of cells from another sheet based off of another cell. For instance, if Cell in sheet 1 = cell in sheet 2 then copy these cells? Currently, I am linking multiple cells manually and it is very inefficient. See attached for sheet layouts. Thanks in advance.
-
Trying to get dates to populate off formulas
Essentially, I need to have users manually enter a start date (this could be either one cell or I could dedicate a column if needed), and based on the needed duration for each tasks the start and end date columns would be generated. For the Duration column, I would like this to populate via lookup table as the durations…
-
Formula to calculate remaining savings to end of project
Hello, I am trying to create a working formula to display remaining savings to end of project. I am using the formula below and seeing and "UNPARSEABLE" error, of course. I'm terrible at these but trying to get better at it. Please help. =([Current Cost]@row-((([projected # of lines])*50.95) *[remaining project…
-
COUNTIF DESCENDANTS of the highest ancestor of the row being queried
Data sits at the third structural level of a data table. I want a column with a value on each row to tell me how many descendants there are from the highest ancestor of that row (i.e. within that structure) Basically, the current row is 1 of how many descendants? So all third-level rows will show the same count value…
-
INDEX function with multiple IFs
Hello! I want to index in the "Mês atual" row the value that corresponds to the passed month and the actual year we´re on, so this column will just have one cell with value. I managed to index the month value, but it doesn't correspond to the right year, it's pulling it from the 2022 row. This is the formula:…
-
Index Match with "Starts with" match criteria
I'd like to pull in a value from a separate sheet if that value starts with "xyz" (for example) and also matches a specific project ID. For example, if I have "xyz" as a value in one of the columns on spreadsheet 1. On spreadsheet 2, the value is called "xyz123." The two values have matching project IDs. How can I pull…
-
Join Collect with multiple criteria
Can someone help me with this formula? It will not work for the life of me. =JOIN(COLLECT({Current Loans Range 5}, {Current Loans Range 1}, "CASA"), [{Current Loans Range 2}, "Y"]), "; ")
-
Data containing parenthesis
Hi, I have data that contains a parenthesis. Is there a special way to reference data that contains a parenthesis in countifs statement? Here is the formula: =COUNTIFS(Category:Category, "System Maintenance (Post Go-Live)", Rating:Rating, "1") Any suggestions? Thanks, Matt
