-
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.
-
INDEX COLLECT function returning #INCORRECT COLUMN VALUE
I have a sheet that contains the info of all of my employees which includes a column for their initials. The training department of my company has a sheet that I have view access to but not editor access. They track the qualification due date of all of my employees and I want to collect the due date on my employee info…
-
Calculating a percentage based on YTD wins
Hi everyone - I'm trying to add a percentage of wins to a dashboard. Initially I thought the easiest solution would be to add a formula to the sheet summary page and i used this but have been getting back #invalid Data Type error. =COUNTIFS([Status/Conclusion]:[Status/Conclusion], "Won", YEAR([Date Requested]:[Date…