-
Perfect Match
Hi all, I am trying to figure out a way on how to match mentors with mentee. I have a survey that i send out to gather the info with a series of questions regarding skills that you can teach(Mentor) and skills that you want to learn (Mentee). How do i match a mentor with a mentee that have mutual interests (teaching and…
-
Need help editing "Status" formula so it doesn't automatically change to "In Progress"
Hi, Hoping to get advice on how to edit the below formula. When the "Start" date becomes the current calendar date, the "Status" changes to "In Progress" even though the % Complete is still blank. I'd expect the status to remain "Not Started" (if the % Complete is blank or zero), and change to "Overdue" when the "Finish…
-
Count Distinct Collect only returns 1, even when there should be none
I have this formula for a Sheet Summary field: =COUNT(DISTINCT(COLLECT(Goal:Goal, [Primary SD]:[Primary SD], ="SET", [Fiscal Year]:[Fiscal Year], ="FY2022-2023"))) Attached is a screenshot of the sheet. It is filtered to show only rows with Primary SD=SET, and you can see that there are none for FY2022-2023. So the Sheet…
-
Extract multiple pieces of a text string that meet a criteria - between parentheses
I want to extract both the 1234 and 4567 from this text string: "First set of data to extract between parentheses (1234) and second set of data to extract between parentheses (4567) and that is it" I am able to pull the 1234 with this formula: =IFERROR(MID([TEXT_COLUMN]@row, FIND("(", [TEXT_COLUMN]@row) + 1, FIND(")",…
-
Project Manager Count
Hello - I'm trying to build out a dashboard where I include a chart containing the project manager and the count of projects they currently have utilizing the PMO workspace. With some of our projects being large, we've allowed for multiple PMs to be on 1 project. Is there a way to modify the COUNTIFS to account for a name…
-
I am looking to use an Index match to concatenate a persons roles
I have a sheet to track workers roles however, some people are listed multiple times as they have multiple roles because depending on the project their role is different. I was wondering if there was a way to use an Index(match( to either concat all of their roles in 1 formula without the use of a helper column. My current…
-
Options for creating a look up tool...
Hello! I am trying to think of all of the ways to create a look up tool in Smartsheet that can be accessed by non-Smartsheet users, without the non-Smartsheet users being able to see the back end. We will only be collecting the email addresses of the non-Smartsheet users and then returning the name and email address of…
-
Comparing one multi-select field against another
Hello Community! I wonder, is it possible to compare one multi-select field (array) against another and return TRUE if any match can be made between the two? Consider the following data set. Currently, I am playing around with: JOIN(COLLECT(Print:Print, MultiSelectData:MultiSelectData, {Returns True When ANY Match…
-
keeping information current / between two sheets
Hi, I have several worksheets, one being the master. I have figured out how to "copy" a line from one to the other, but when that line changes, it does not update the other sheet. How do I ensure the two stay in sync? as an example - in the secondary sheet, the line changes from planned to completed, but that change is not…
-
Is there a revised formula that will allow me to show more accurate training dates?
I have a formula in a training matrix that compares dates of when a document revises and when the training was carried out for employees. =IF([Revision Date]@row > [employee]93, true, false) However, this returns a red font date if the revision date is greater than the training date, even if the employee was trained prior…