-
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…
-
If Statements
Hello All, I am attempting to create an If statement that raises a flag if two rows have the same location and the same date. This submission sheet will be used when people visit a location and we do not want any double bookings. So I was planning on having a formula raise a flag and then have a report that looks for the…
-
Index/Collect/Match help
Hi I have a sheet that has epics, stories, tasks and I want to extract only the epics to another sheet. layout is simple Name IssueType Epic 1 Epic Story 1 Story Story 2 Story Task 1 Task Task 2 Task Epic 2 Epic Story 3 Story Story 4 Story Task 3 Task Task 4 Task I want the other sheet to only have Epic 1 Epic 2 thanks so…
-
COUNTIFS Formula not working when I add an ISBLANK statement
I have a COUNTIF formula that I can't get to work once I add an ISBLANK statement. This is the fomula that works: =COUNTIFS({Payment Type}, =$Status@row, {Case Date}, MONTH(@cell) = 8, {Case Date}, YEAR(@cell) = 2020, {Status}, ="Completed") When I try to account for rows where the Payment Type is blank, I tried this…