-
If OR and SUM formula
Hi, I created this formula but I'm not sure why I'm getting the error message "INVALID DATA TYPE" =IF(OR(Name@row = "AB", "CD"), [Number1]@row, SUM([Number2]@row:[Number5]@row))
-
Data transferring
I'm trying to transfer information from sheet one to sheet two, then update the information on sheet two, and then that information to be transferred to sheet one.
-
Seeking Assistance - Average Days Open, by Status, Month/Year
Hi All - I'm trying to calculate the average # of days for data set, where the Status is "X", and the M/Y = "Y". I'm good with "AverageIf" = label@row, but not sure how I can incorporate the date piece. Thanks in advance! Sharing some snapshots of the data, all columns (Total Days Open, Completion Status, Leave Blank...)…
-
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…
