-
Is there a way around an Invalid Operation when using Dates generated by formulas
Hi, I am trying to use this formula =IF([Next inspection due]@row <= TODAY(0), 1, 0) then mark it with a flag. in the Flag Due Date column. Every time I use this formula I get INVALID OPERATION because the Next inspection due column is created from a formula of a formula of a date. Date6 is add on 6 months, Date1 is add on…
-
How to have status in parent row change to complete when all child rows are complete.
Is there a way to make the parent rows change based on the child rows? For instance, I want the parent row to show as complete, once all of the rows below are complete.
-
Need Help with Index Collect Formula
Hello, The formula i am trying to put together is below: =INDEX(COLLECT({HBC Master File Product}, {HBC Master File Formula}, HAS(@cell, [HBC #]@row), {HBC Master File Plant}, CONTAINS(@row, "CBG COLD SPRING - G")), 1) I want to search another sheet (HBC Master File Product) in the formula column to match the HBC#, the…
-
In need of formula to add multiple durations together from one sheet into one cell on another sheet
I have a list of hundreds (thousands) of rows of data, most of which have one duration per unique identifier, but on occasion, there are multiple durations (usually never more than 6) for the same identifier. I need a formula which will compare the identifier on Sheet A to the identifiers on Sheet B and either: If only one…
-
Checkbox Question
Hello, I'm trying to make a formula so that when my "Task 100% Complete?" column checkbox is checked then it will automatically change "Fab Status % Complete (Needs Real Numbers)" column on that row is changed to 100% automatically. This saves the user of having to check the box and change the Fab Status column to 100%…
-
YEAR function in cross-sheet reference COUNTIFS
I am working on a metrics sheet where I'd like to count the number of items that a given department has implemented within in a specific year. In this formula {Date Implemented} is a cross sheet reference to a date column (MM/DD/YYYY) and the matching criteria is a cell that contains the year value, in this example "2024".…
-
Need help on a simple IF/AND formula but already spending too much time on it
Looking to show 'Delay' for tasks that have %complete <100% and Finish date < than Today (). Tried multiple variations but some records come right and some just ignore the %complete record. Why? Formula is: =IF(AND([% Complete]@row < "100%", [EMD Finish]@row < TODAY()), "Delay"). This formula is showing "Delay" on tasks…
-
SUMIFS with Date Range & Condition
Hello Smartsheet Community, I am hoping you can help me with this simple formula. =SUMIFS([Start Date]:[Start Date], <=DATE(2024, 3, 1), Timeline:Timeline, "Tender Submitted", [Quote Total]:[Quote Total]) It is coming back as an invalid operation. Can you help? Thank you in advance.
-
Interest Calculation based on multiple column
I am attempting to calculate interest based on a past due value in one of 4 columns. I need this formula to calculate all 4 columns because the amount may appear in one column this week and move to another the following week. I am able to get it to calculate using one column but when i attempt to add more columns i get an…
-
Incorrect WeekNumber or(??) Year when trying to return a 'Week Number of Year' calculation
I am trying to return "Week Number of Year" (I want two digits for the Week Number hence the workaround in the formula) and all my weeks start on a Monday. I have used the following formula which works great until you get to Monday 30 Dec 2024 where it returns "01 of 2024" - clearly wrong as Monday 01 Jan 2024 should be…
