-
How to automatically add NA to a date column in 1 case, which is updated manually in other cases?
Hi, So, I have a date column, say Date A. I have another column say 'Condition A'. When 'Condition A' column consists of a value 'ABC', then Date A column should auto populate with 'NA'. In other conditions when Condition A column has 'XYZ' or @'PQR', Date A column needs to be filled manually by the smartsheet user. Now,…
-
Distributing Savings Across Months Based on Term Date and Fiscal Year
Hello My Super Intelligent Smartsheet Community! I have attempted to solve my problem by breaking it up into pieces and I'm stuck. I think I've been trying to solve this for too long and my creative juices are fried. I have Service Providers we have contracts with. Each contract has a Contract Start and End Date and our…
-
Lookup on a Many to one sheet
Okay, this may be a bit tricky, so I would appreciate your help. I will be creating a new sheet, and accessing both of the sheets below. Let's call the screenshot at the top, SheetA, and the one at the bottom SheetB. Now, SheetA is a "one to one" with the TDLs listed in the column on the right, there is only one entry on…
-
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…