-
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…
-
How do I count how many cells are last month but not considered expired?
I am working on a sheet for currency of qualifications for our employees. I have the equations that will tell me how many employees are a month before they are due, how many are due this month, and how many are in the past but now I have to find and equation that will let me count how many qualification are only one month…
-
Volunteer Allocation
Hi All, I am attempting to find a solution for a specific manual task that is error prone and very time intensive - We need about 300 volunteers occasionally on short notice and need them across multiple cities in 20-50 specific locations (we do not know these specific locations until last minute). I have built a survey…
-
COUNTIFS Description
I believe the description of COUNTIFS is misleading and basically just incorrect. It does not properly describe what it actually does. It says it counts the number of cells but it doesn't. It counts the number of times all given conditions in their respective ranges are met. One match of all given conditions in their…
-
Using Collect with count if and count if NOT
I have the following formula I'm trying to run =COUNT(COLLECT({Target Live Date}, {Checkbox}, "1", {Type}, "Native", {Status}, NOT "Published")) Count cells with a target live date, where the checkbox is ticked, the type is native, and the status is NOT published. All seems to be working well until the NOT part. Any clue…