-
Collecting Average Between 2 Ranges
Hi all! I am trying to collect an average over the last 12 rows (including the current row). To do this, I created 2 additional helper columns (Line-ID and Row#), and trying enclosing the condition in an AND clause, however this seems to return unparseable. I think I am on the right path or way of thinking here, but that…
-
Formula returning Invalid Column Value
Hello, I'm struggling with a formula that is returning an in valid column value error. All columns are Text/Number. Sheet 1 has one row for each project. Sheet 2 has a column that may have multiple project numbers in the same field. I need to return the value in the Status field (which is a drop down) if the project number…
-
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".…
