-
True count of Children(), not just non blanks
Doing project roll up and trying to use the formula below to roll up a complete status. =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "Not") However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non…
-
Multiple cells locked to single formula in same column
I'm relatively new to Smartsheet and use it as a tertiary tool, so apologies if this is a 'basic' question. What I want is to tweak a formula on one row of a column and have it apply all other rows that reference that cell in the same column so I don't have to copy the tweaked @row + Parent, Children, or Ancestors…
-
Adding Multiple Criteria + Does Not Include
Hello, Smartsheet community! I am stumped on a function. I have attached a photo of a dummy sheet with the same functionality I am trying to achieve. Using the example, how would I set up a formula which adds together the lunch selections of turkey, ham, and roast beef if they DID NOT choose soda as their drink? Lunch…
-
Is there a way to have a column automatically pull data from multiple columns based on the month/yea
I have a master sheet that includes data for multiple projects from different months. I'm trying to generate a column that pulls the data from the current month. The intent is to create a cell that populates automatically and can be referenced on a dashboard. I have tried to use the index/match formula with no success. I…
-
How to get JOIN working when referencing another sheet?
I'm using the JOIN formula, if I put the formula on the same sheet as my column references, it works just fine, however when I move this to a separate sheet and update the column references to the other sheet I get the error #UNPARSEABLE. I haven't removed any of the formula delimiters only which sheet I'm referencing. Any…
-
Health Bubble Turn Colors when Status is Updated
Hello, I am new to Smartsheet. I am trying to get when I change my Status to "Not Started" I want the Health bubble to automatically turn grey. And when I change my Status to "In Progress" the Health bubble will turn yellow, etc. So far I have: IF(Status1 = "Not Started", "Gray", " "). I want it to change color for each…
-
Completely stumped on this formula
Hello, I'm trying to be able to identify when an upsell comes through for a specific function. But so far everything I've tried hasn't worked. The sheet I want the information to show up on isn't the sheet that has the data so I have to reference a separate sheet. But basically what I'm trying to do is: =IF(AND({Customer…
-
Using COUNTIFS to find a partial text match in 1 cell + other conditions
Hi Smartsheet Community, I'm having trouble with a COUNTIFS formula and hoping you can help. I'm trying to count all rows that are assigned to a specific person. Because it's possible to have multiple assignees for a task on my sheet, I need to do a partial match for the assignee name. Here is the formula I've created:…
-
Help creating formula to update inventory automatically
The following is our brochure requests sheet. Right now we manually update our inventory each time we receive a request. Here is a snapshot of the new inventory sheet. I'd like to create a formula that automatically subtracts 100 from Spanish Qty each time the Request Completed is checked, the Request Denied column is…
-
How to make a manual Row ID based on conditions?
I'm not even sure if this is possible but I invite all the formula wizards to take a crack at it! I have this intake sheet with 10 rows, all that have a corresponding auto-generated Row ID from the system column. Row 1 is 23-001, row 2 is 23-002, row 3 is 23-003, etc. When a project gets approved, I want another column,…
