-
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…
-
How to Consolidate Entries from Multiple Forms
I've seen this question asked before, and the same answer is always given, "use an update request." Is there a way to consolidate information, because I don't think update requests are the best solution for our process. I have multiple forms with different types of information being entered at different times by different…
-
Nesting multiple IF statements inside OR statement
-
Reassign Contacts with Automation
Our accounting team regularly reassigns projects to new managers/staff and I need to find a way to automate the change once a date has been reached. In the example below I would want the contact to change from Joe to Jim once the Estimated Transition Date has been reached. The problem is I need this to be a 1 time write…
-
Formula issus while automation
Hi All, I am currently having an issue for one o my workflow, I will try to describe briefly: I have a ticket system that we use for one customer. Whenever a ticket is completed in the "Open ticket sheet" by our Team, the line is moved to an "archive sheet" thanks to an automation. In the open ticket sheet, I have this…
