-
IF, INDEX, MATCH formula help
I need to add an IF statement into an INDEX MATCH formula that pulls the system size of a column if it is not blank. This formula will be on sheet #1 and needs to be an IF, INDEX, MATCH. Sheet #2 will have the actual system size column and Sheet #3 will have the target system size column. The formula should pull from sheet…
-
Return highest value
I need to be able to create a formula that will return the highest value based: I want it to match the Auction Item and return the highest value in the Bid column and the name of the highest bidder In this instance, I want the result to be Concealed Carry class - $100 - Becky Wilson Lydia Gillis Photography - $500 - Becky…
-
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.
