-
Status Colours - comparing dates
Hi Quite new to SS, but have been tasked with trying to update a current sheet to show a colour status column. I have 2 columns which hold dates, with some of the data been pulled in from other sheets, thats need comparing. If someone can help with the formula to show Green, amber, red status dots depending on the below…
-
Using a Relative Date in COUNTIFS Formula
I'm looking to use a relative date in my COUNTIFS formula (ex: In the next 7 days). This needs to be evergreen so I don't have to be updating the dates every day to have an accurate count so I need a relative date functionality rather than using a lesser than-greater than formula.
-
average(children()) not working when cell in child row is a calculated field
Hi, =Avg(Children()) not working when cell in child row is a calculated field as shown above. Is there anyway to fix this.
-
Parent status (RYGB ball) changing based on child status (dropdown)
I am looking for help with a formula where I have a parent row and then multiple children rows below. The Parent has an overall status that is a 4 ball symbol of red, yellow, green, blue) My child rows are using a dropdown menu called "Confidence" with percentages as the values (10%, 20%...100%). I'd like to set the the…
-
Help with DISTINCT Function
I need to create a list of unique names pulled from a column on a different sheet. I used this example to get things generally working. However, I'm not ending up with a truly unique set of names. The formula in question is: =JOIN(DISTINCT(COLLECT({Fruit}, {Fruit}, @cell <> "")), ",") + "," Here's an example of how my…
-
IF - THEN - IF/AND Formulation
Having a hard time with formula logic. The setup for a RYGB column: If the row is a parent row, I want it to do (X), where is as an elaborate string of calculations based on a another column's values If it isn't a parent row: AND IF it is complete, then I want a BLUE Harvey ball, else, a GREEN Harvey ball Can this be done?…
-
INDEX and MATCH help
Hello, I am building a dashboard to pull information off multiple sheets, and have created a separate "config" sheet to use formulas and organize the information to make widgets easier. I am looking to pull info from a date column in a project plan, based on a unique task name. I have a column in the config sheet that…
-
Current Milestone task name for the current row
Hi Relatively new to smartsheet and getting to grips with sum of the hierarchy functions I want a formula that returns the task name for the milestone of any given task. or the task name of the last child any help very much apreciated
-
Combining Join Collect & Children Functions
I'm trying to join all the values in children rows only if another column criteria is met. Here's what I have so far and thought should be working.. =JOIN(COLLECT(CHILDREN(), [Top 10 Keyword]:[Top 10 Keyword], "1"), ", ") Also, what's kind of strange is the formula works perfectly when I change it to…
-
Combining a Nested IF Formula and Searching for the First 4 Numbers in a String
Hello Smartsheet Community, I am trying to build a Nested IF formula to assign a site name depending on the first 4 numbers in the screening participant ID. So for example, I would want the formula to identify "S401" from the string "S401001" in the Screening ID Column and assign the name of "01/New York" in the Site…