-
Add days to a date
Hi, I am trying to create a column that adds a certain number of days to a date. When I use =[Task start date]@row +7 . It doesn't add 7 days to the date, instead just adds the number 7 to the end of the date. I have also tried to use the WORKDAY formula as an alternative, =WORKDAY(Task start date]10, 5) . This results in…
-
Formula to populate a "Year" if Create date falls between 2 column dates
I created a form that request the Calendar year for funding. They range from 2022 to 2028 and each year has Quarters. Sample quarter is between April 15 - July 15 so if I want the formula to populate me a Calendar year 2022 if the create date falls between the April - July dates. Here is the formula that works but is…
-
Count Days Since Ticket Opened
Hello, Needing to calculate number of days since line item appeared when Date Submitted column is "Auto-Number/System Generated Column Created (Date)" -- and then display "Closed" when Checkbox is checked in Complete column. Can't get the "Complete" to appear -- just goes blank. =IFERROR(IF(Complete@row = 0, TODAY() -…
-
Using Index Collect formula when there are multiple matches in the source sheet. (#Invalid Value)
Hello, My goal with using INDEX(COLLECT)) was to obtain information based on 3 different criteria, but in my source sheet there are multiple matches for the three criteria specified. What do I need to add to the Index, Collect formula to overcome this or do I need to use a different formula combination? Below is the…
-
Total sum of results of a formula?
I have a formula to calculate payouts as follows: =IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 75000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <= 250000), "$2500", IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 251000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <…
-
Red Flag for Overdue Tasks, without also Flagging non-dated ones
We are trying to use a formula to do the following: •tasks that have been cancelled are gray •tasks that are past their due date are red •tasks whose due dates come up within the next 5 days and are not yet complete are in Yellow •tasks which are already completed or whose start date hasn't hit, and are due in 14 days are…
-
Combined IF, AND, & OR Statments
I am having trouble with a combined IF, AND, & OR formula. The formula is to check five columns for a Yes "Y" value. For two of the columns only one value has to be Yes. If all are true then return a 1, if not then return a 0. Where is my formatting wrong? =IF(AND([SPC Received]@row = "Y", OR([SDS Received]@row = "Y",…
-
Formula to create symbols based on parameters
Hello Community, I'm attempting to create a formula that will create a green, red, or yellow dot symbol based on parameters and values within given cells. The formula I have thus far is below but I continue to receive error messages Any help would be appreciated. Thanks!
-
Dashboard with Filters - Best Practice
Hi all, I am pretty new to Smartsheet and will need your help to build a dashboard. What is the best practice to create one? GRID I have multiple salespeople in my team, each of them have their own grid. The grid looks something like this: ID | Date | Stage | Comments I am creating forms for them to fill up on a daily…
-
COUNTIFS across columns with multiple options for one column
I am trying to write a formula to count the number of columns across five nonadjacent columns that contain "Verified". The fifth column may contain "N/A" and also needs to be counted. In the example, 5 should be the total count for both rows. I've tried adding multiple COUNTIFS, and it doesn't work. Thanks in advance for…