-
CountifS with Distinct
I need a function to count how many time a territory as unique SIDs in a sheet. I tried the function below but it won't work. =COUNTIFS({Total URV Visits Range SID}, DISTINCT({Total URV Visits Range SID}), {Total URV Visits BEC}, District@row)
-
Redefine a formula for status and date
Changes needed to my current formula. There is just too much going on, I want to simplify this. My current formula in the "Certification Complete" is below. Now I also wish to change these colors to words: Green - Complete Yellow - In Progress Red - Not Started In addition I want the Certificate date field to be automated…
-
Help with INDEX/COLLECT with a MAX/COLLECT
I am attempting to create a few reference columns using an INDEX/COLLECT function and incorporating a MAX/COLLECT because there is a 1-Many relationship on the RTO Queue ID in the 2nd sheet so I would like to use the row with the MAX Fully Executed Date. Below is the formula I am using: =INDEX(COLLECT({IA Assigned}, {RTO…
-
How? Column Formula to Pull Level 1 Parent Task Name
Hi Smartsheet Community, Please see image of setup! I am trying to create a column formula for the column 'TEST' that pulls the name of the Level 1 parent task for each row. Essentially, I want every cell in the 'TEST' column to display the name of its Level 1 parent, such as "baseline estimate" or "estimate R1," depending…
-
How to remove the unnecessary commas?
I worked out this formula with someone during a ProDesk session but they weren't sure how to remove the commas when they weren't needed so I'm hoping someone else maybe has an idea! The formula: =JOIN([Inspected Employee's Name 1]@row + ", " + [Inspected Employee's Name 2]@row + ", " + [Inspected Employee's Name 3]@row +…
-
Help with a weekly updated formula
Hello! I'm still a beginner smartsheets user, and while I am decent with excel the live updates is still very new. I'm trying to create a formula that will pull information on a weekly basis, M-F, that updated for each week and didn't pull from the previous week at all. For example in this screenshot, I would like a…
-
Countif with multi values and variations
I have countifs statement but then need to account for multi-value dropdown list and wonder the best way to capture the values. So counting projects status of in project - with a business value of 6 options (multi-value). So Business Value column has multi-value options from a drowndown. I really only care about the total…
-
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…
-
Check box if matching value in other sheet
Hi All, I am attempting to write a formula that would check a box if a value is present in another sheet, and I can't figure out exactly how to write it. I am thinking it would be a combination of IF, INDEX, and MATCH. I have two sheets, both of which have a column called "Protocol ID." I want to check a box if the…
-
Lock the cells based on the duplicates
Hello Experts, I have 3 COMMENTS row duplicated and 1st COMMENTS row is having translation captured for the 3 language headers. However, there is 2nd and 3rd COMMENTS row. I have already enabled duplicate logic to highlight the row in YELLOW color, so the 2nd COMMENT row is highlighting in YELLOW. Is there any possibility,…