-
Formula results started flipping back and forth
Hello, My team has been using the sheet I built for months, and on or around 9/12, some of the formulas have gone haywire. I have multiple components within a project, and once all components are complete, the project stage will flip to Complete. I use a JOIN(COLLECT) function in one column and a helper column that…
-
Week number comparison
=IF($[STUDY NUMBER]@row = "", "", IF($[STUDY NUMBER]@row = "EMPTY", "", IF(AND(WEEKNUMBER($ARRIVAL@row) <= [WK 39 SEP 26]$1, WEEKNUMBER($[END DATE]@row) >= [WK 39 SEP 26]$1), $[# FLOWERS]@row, IF(AND(YEAR($ARRIVAL@row) <= 2021, WEEKNUMBER($[END DATE]@row) >= [WK 39 SEP 26]$1), $[# FLOWERS]@row,…
-
Index Match/ V look up or Data Mesh?
Hi, I'm using Smartsheets to input all associated software release notes across many different software versions (each software version has a separate Smartsheet). My issue is that sometimes certain tasks IDs appear in multiple versions of software. e.g. Task ID: 1234 may appear in software version V10 and V11. I want to…
-
Separating out a date by Quarter and Year
Hello, I have an End Date column (which is formatted as mm/dd/yyyy). I need a formula to show as Q1-2022 (example) for any given date in the column. In excel, I was able to find a formula that works but transferring it to Smartsheet I receive an UNPARSEABLE error message. That formula is: ="Q"&INT((MONTH(End…
-
Adding Teams user data to a Form
Hello, I'm wondering if anybody knows if there is a way to pull in somebody's Teams user data into a smartsheet form. I have a Form that is published in Microsoft Teams and has a couple of drop down boxes for people to use. One to identify themselves, and another to identify another employee. I'm trying to create a way so…
-
Subtracting Dates to determine what is less than 90 Days
Maybe I need to use a different formula, but this was successful in returning "Short" for everything, rather than anything under 90 days. We are trying to call out short start projects or those that require less than the average 90 days, so a difference of 90 or over should return nothing. =IF([Anticipated Activation…
-
Nested IFs using AND
Basically I am creating a report on our Asbestos Register requested by our H&S team basically they would like the following but in a formula. If total score is greater than 15 display "Band A" if total score is greater or equal to 10 display "Band B" if total score is less than or equal to 14 display "Band B" if total…
-
Change Symbols with respect to due dates
I want to do this if Due date has 3 or less than 3 days left ="Yellow" if Due date has passed ="Red" if Due date has more than 3 days left ="Red" if Due date row is empty = "Gray" The formula I'm using =IF([Due Date]@row = TODAY(3), "Yellow", IF([Due Date]@row = TODAY(2), "Yellow", IF([Due Date]@row = TODAY(1), "Yellow",…
-
Categories item number given
Hello Dears, I hope my email finds you well, I have seen your videos about Smartsheet and they are accommodating, I am stuck in a function I need for my Smartsheet if you can support me. The idea is, I have a category and in this category, I have 5-6 items and need a function that shows me in each column next to the…
-
COUNTIFS & AND + <>
Hello, I appreciate your help in fixing this formula. I want to exclude from counting any blank cell for one column & any cell with "Center" from another column. The one for excluding "Center" is not working. =COUNTIFS(AND([Deliverable(s)]3:[Deliverable(s)]137, <>"", [Responsible Party]3:[Responsible Party]147,…
