-
LINKED CELL FORMULA
I have a template sheet which is used for many other sheets but my managers keeps moving the goalposts for part of the reporting meaning i have to update a formula on the template then copy and past into the other 18 sheets currently in use. Today i set up a new sheet i thought i would try linking the formula cell to the…
-
Adding months automatically
Hi all I am trying to automatically add a 6 month review date to a start date, it is working fine until a start date is past June, to which I get a #invalid value error. The formula I am using is: =DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)) So 1/6/19 becomes 1/12/19 but if I try with 1/7/19 I get #invalid value instead of 1/1/20.…
-
How to enter specific text if the cell contains a partial match?
My If statement: =IF(OR([Task Name]@row = "Discovery", [Task Name]@row = "Analysis", [Task Name]@row = "Build", [Task Name]@row = "Test", [Task Name]@row = "Deploy", [Task Name]@row = "Design"), "PMO", IF(OR([Task Name]@row = "PER", [Task Name]@row = "PRT", [Task Name]@row = "CK1 RTS", [Task Name]@row = "CK2 RTB", [Task…
-
Impossible to visualize groupings more than 2500 rows...
Hi, Im searching over the internet a variety of things (data) from a variety of types (countries, format, source, etc) and some of them are validated (true) and some dont (false) and inserting this information in a Smart Sheet page. On the other hand, I have this REPORT, reading from that sheet i have just mentioned, and i…
-
Why can't I convert this formula into a column formula?
Hello, I have sunk quite a few hours into trying to figure out why I can't turn the formula below into a column formula. Does anyone wiser than I have any ideas? =IF([Start Date]2 > TODAY(), "Blue", IF(AND([% Complete]2 < 1, [End Date]2 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"))) The formula…
-
Use INDEX COLLECT to identify Row and Column of another sheet
I have a sheet that is filled out by an individual and then when the submission is complete, it will create a unique code. I want to copy their data over to another sheet on an individual basis. Context is each week the person will fill out their form and update their results. Based on their email address and the week they…
-
IF Formula with multiple layers
Hi community, I'm looking for help on creating a multi-layered IF formula for the following guidelines. Anyone an expert that can help me build it out? IF amount is > $100K Then this should be 4 IF amount is <$100K - $125K Then this should be 6 IF amount is <$125K - $150K Then this should be 8 IF amount is <$150K - $175K…
-
Help with nested If, Then Statement
Hi, I inherited Smartsheet templates from a staff member no longer with the organization and am trying to understand the formulas driving our metrics and dashboards. A column formula was written to generate schedule risk as follows: The sheet columns: Can someone please help me understand the logic in plain speak? I want…
-
Formula using the RYG status balls
I'm trying to write a formula using the RYG status balls. These are the criteria: Green= If the "Deployment Complete" box is checked Yellow= if there is a date populated in "Module Go-Live Date" Blue= for anything else (deployment is not complete and Go-Live is not scheduled) I only got this far with the formula and (of…
-
Need to check a box if any of these are true
Hi, I'm trying to check a box if Sales Status equals either "Deployment Prep Call - Scheduling", "Deployment Prep Call - Scheduled" or "Verbal Agreement" The formula works for one of the three choices: =IF([Sales Status]@row = "Verbal Agreement", 1) However, I'm getting an INVALID DATA TYPE error when I have all three…