-
Struggling with INDEX and MATCH
Hello, I am trying to automate task assignment using the INDEX and MATCH functions. My formula is as: =INDEX({Order Sets Team Contact Roster Assigned To}, MATCH(Role@row, {Order Sets Team Contact Roster Role}, 0)) It seems to work for one role but produces a no match error for the rest of the roles. Please see attached…
-
Count multiple blank columns
Hello, I have a task board with multiple columns like Original Due Date, Current Due Date, Goal, and Status. I am trying to come up with a formula that "audits" the data for blanks so we can easily see that Person A has 5 tasks that have blank columns they need to fill out. In the formula below [Column7]$20 is the person's…
-
Countif
Hello all! I am trying to use COUNTIF to count all the cells in the column that say a certain product name. Is this possible if I am trying to count something other than numbers? In the screenshot I attached, I want to count all the "B2Q-B1ETT2 - B2Q Tester" in the "Items Shipped" column.
-
SUMIF Formula - Incorrect Result?
Hello, I was looking over the SUMIF function yesterday on this page and noticed the incorrect result for the first row example. Am i wrong or should the correct result be $2303.00? FormulaDescriptionResult =SUMIF([Price Per Unit]1:[Price Per Unit]3, >20, [Transaction Total]1:[Transaction Total]3)Revenue Gained. SUM…
-
Drop down list data from column in another sheet
Hello Users, I am very new to SS and was wondering how I might go about achieving the ability to link a cells data to an entire column in another sheet. For example, I have a "Current Jobs" sheet and a "Distributors" sheet. When I enter a new job in Current Jobs, the Distributor field in current jobs must only contain…
-
Formula to Outdent a row, when a certain status is reached
Hi all, I've been using Smartsheet for a while now, love it, but I'm a bit rubbish with formulas. I'm trying to have tasks 'de-parent' from its parent task (Outdent to '0') when a certain status is set. I got to this formula (to no avail, but to help illustrate what I'm on about): =IF(Status143:Status159)="COMPLETE…
-
Critical Path Indicator
Are there any options in Smartsheet that would allow me to understand in the Grid View if a task is on Critical Path? Is there a Column type? Formula? etc...? or some type of flag beside just the color of the Gantt bar in the Gantt view? We are trying to filter our projects for Critical Path tasks and our only workaround…
-
Count Unique Values
I have a master sheet that is essentially a list of all the schedules we currently have dealing with projects. Each project has its own project number. All i am trying to do is get a number of the projects we have scheduled to compare to the actual number of projects we have in our queue. Now for the tricky part. Some…
-
Change RYBG Color if the year is Greater than the current year
Hello, I currently have this: =IF([Wants refresh?]2 = "Yes", "Green", IF([Wants refresh?]2 = "No", "Gray")) Which is working fine, but now I want to add that if the column Refresh Year has the year greater than the current year (2019) for it to turn red. I am not sure how to write that. Any ideas? Thank you!
-
Formula help recognising multiple words
Hi, I have a formula that looks for a specific word or variance of the word, is it possible to look for more than one word? The formula i have is =COUNTIFS(Response1, FIND("shower", LOWER(@cell)) > 0) This works for the word showers or shower which is perfect, however am i am able to count if the cell contains either…