-
Help please!
I'm stuck on a formula. I'm trying to count data in a column in another sheet when the date in a separate column in that sheet is 2025. I think I'm getting stuck on having to use the IFERROR function with the year. Here's my metrics sheet And here's the source. I want to count the cells in Quote Value when Quote Complete…
-
My formula is giving me the #INVALID VALUE message and I'm not sure why
Hello! I want to have a column that returns a value of 1 if the date in another column is 6 months or more in the past, and returns 0 if the date is less than 6 months in the past using the following formula: =IF([Date Accrued]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, DAY(TODAY())), 1, 0) The formula has resulted in…
-
Formula to return a number of values that have entered in the last X amount of days
I'm looking for formula help to get me to an end point on a dashboard that would look something like this… Not sure if a report or metric sheet would be best way to get to this point (I prefer a metric sheet if possible) I have a sheet that is collecting the names of these units as well as the date the info was submitted.…
-
Using Collect to pull data from the same sheet
I usually use Index(collect to reference separate sheets to pull data, but I'm trying to apply the same logic for pulling column values from the same sheet. I tried this as an Index(collect formula =INDEX(COLLECT([QTY on Order]:[QTY on Order], [Ref PN 1]:[Ref PN 1], [Part Number]@row),1 & a collect formula on its own,…
-
How to calculate days a ticket is open/closed
I am trying to calculate how many days a ticket remains open but I also need that formula to stop counting if the "Ticket Closed Date" column is filled in. I have tried a couple but they keep coming back as errors. There are multiple selections for the Status of Ticket column... Work Has Not Started, Work Has Begun,…
-
Star if Several Criteria Match
I want to Star the cell if the center at row matches any of our centers. The formula I am using: =IF([Center]@row = "Carville", 1,IF([Center]@row = "Centennial", 1,IF([Center]@row = "Charleston", 1,IF([Center]@row = "Clearfield", 1,IF([Center]@row = " Earle C Clements", 1,IF([Center]@row = " Edison", 1,IF([Center]@row = "…
-
I need to Sum a column from a reference sheet and display the number in my primary sheet
I am attempting to build a column formula that generates the sum of hours for a given phase (task). This is an interaction of our Main project grid and the Timesheet grid (it is one of the advanced reports Resource management offers) from Resource Management. Our main project should display the number of hours incurred for…
-
Strange INVALID VALUE error
Hi everyone, I've encountered a problem in Smartsheet that I tried solving for a long time without success. I was creating metrics for a sheet, and I made a bunch of summary fields that contained my formulas. They all worked as intended when I created them. About a month passed without anyone touching them, I went back to…
-
Workflow action to set a date to a relative date or static date
I am requesting a formula that would allow me to update a date with something other than today which is done with "record a date". I may want to record 3 days in the future or possibly a fixed date such as "12/25/2026". This would allow me to set fields that can be used for alerts while leaving them editable since they…
-
Workflow Action to increment a Cell value by some number
As a programmer there are many cases that I would like to take a number in a cell and add some value to it. I dont have a particular use case in mind currently, but this allows another tool in the box for coding complex systems.