-
Is there a way to use LOOKUP to get a total qty for the reference if it's been entred multiple times
Hi, I'm really not sure if I'm going about this the right way, but I'm trying to create a 'Bill of Materials' within Smartsheet for a construction project. As per the two pics attached, I would like to get the value of a product that is mentioned multiple times in a particular column. For example, 4 of PRODUCT 2 is going…
-
OR formula help
I tried to modify the OR formula and combine it with a function involving RYG symbols: =IF(OR(IF(TODAY() - [Order Due Date]2 > 0, "Red"), IF(ISBLANK([Order Date]2, "Red")), IF(TODAY() - [Order Due Date]2 > -4, "Yellow", "Green"))) There are basically 3 columns: Order Date, Order Due Date, and status (RYG). I am trying to…
-
Smartsheet formulas with multiple columns
We're using Smartsheet for some change management, and I'm new to both Smartsheet and spreadsheet formulas. What I’d like to do is have a formula looks at a column that indicates the type of change (Major, Minor, Emergency, etc.) and then looks at a number of check boxes and adds an approval in a separate column. So, for…
-
Help with check boxes
Hello, I am trying to figure out the formula to get a check box to auto check when two other boxes are checked. In the image attached, I'd like to link the check Boxes in line with Person 1 and Person 2 to the check box in line with President Elect. How do I do that without linking all the other lines? Thanks
-
Formula vs Conditional Formatting
I was hoping conditional formatting could solve this but can we do IF/AND on conditional formatting? If not, what would the formula look like? If the End Date is in the Past AND % Complete is NOT 100% change End Date Font Red. Currently, I have a formula that will flag "At Risk" column if the date is in the past and %…
-
IF(OR Formula Help
What am I doing wrong? If Required Column, Rows 23-34 are "Yes" OR "No" THEN show "Yes" symbol, if NOT show "On Hold" This is the formula I'm using: =IF(OR(Required23 = "Yes", Required23 = “No", Required24 = "Yes", Required24 = "No", Required25 = "Yes", Required25 = "No", Required26 = "Yes", Required26 = "No", Required27 =…
-
If all children are not blank then enter a phrase
Hi I am having trouble writing a formula that will return the phrase "Completed" in a cell of a parent row if all the children are not blank. If there are some blank cells then it would return "Not Completed". I am trying to get the word in the parent row in the column "Opera Analysis Code", if all the children have…
-
If then statement for Risk Management Matrix
Hi Folks - I'd like to feedback to write a formula that evaluates the following: =IF(Cell A1 "Acceptable" then??? Acceptable Tolerable Undesirable Intolerable "Then"... the statement should take the word "Acceptable" and give it a value of 1, repeating this same evaluation such that "Tolerable" receives a value of 2,…
-
Date-based RYGB balls automation
Hi, For the past couple of days, I have been trying to automate my RYGB balls based on Target End Date and Actual End Date columns. I've been reading this article, but I cannot seem to get the formula right. These are the conditions I am trying to satisfy: * BLUE = if the Target End Date cell and Actual End Date cell are…
-
Persistent formula cells
MS Project has a lot of built in features that SmartSheet just doesn't have. Such as calculating if something is late, setting a deadline that flags if the date moves, calculating AWC and SWC, etc. To that end I have created a bunch of hidden cells that do all the needed functions and checks, and then built the…
