-
Index Match with multiple Parent/Child rows in a sheet.
Hello all, I'm looking for some advice on a tiered IF statement using index/match and CHILDREN formulas. My case: I receive numerical data from another metric sheet that copies it snapshots into this archive once a month. There may be times where we need to edit the historical data. To ensure the % columns are updated I…
-
Google calendar like option or work around?
Hey I have 2 columns that show different dates that I would want to see together in the calendar view. Has anyone figured out a work around for this or a clever formula to make this work? Hypothetically, for example: I would like to see the Packaging Release Date, Photography Shoot Date, and Copy due date.
-
If formula for checkboxes
Hi, I am having trouble with the correct formula sequence for check boxes to stay checked through out the workflow process? I want the check boxes to stay check as it moves along its stages. current formula I am using is: 100% Rec'd column =IF([Workflow Status - BitRip]@row = "Received", 1) Shop Installed column…
-
Dropdown multi-select columns list comparision
I have two columns that are dropdown multi-select. One column includes a list of all possible values (example: letters A-Z) while the second column includes a list of some letters (without duplicates (example: A, B, C). I would like to create a column that displays the missing letters (example: D-Z). How would I go about…
-
OR( ) for multiple criterion ranges within INDEX/COLLECT/SUMIFS
Hello, any tips for using OR for multiple criterion ranges? The formula below outputs #INVALID DATA TYPE. =INDEX(COLLECT({Task Name}, {Details}, <>"", OR({X Budget}, {Y Budget}), <>""), [Expense #]@row) The goal is to only pull tasks into the new sheet that have a value for either budget type (X, Y).
-
Index collect = incorrect argument set
Hi there, I am using index collect and cannot understand why I am getting an error message "incorrect argument set" =INDEX(COLLECT({Rack CMDB}, {Asset Tag}, [Asset tag]@row), 1) Cross sheet references are connected to the same sheet. Column format is Text/Number Many thanks in advance Sam
-
SUMIFS Delivery Date >= Inventory Date
I'm attempting to write a SUMIFS formula where one of the criterion is to compare one date to another. It is returning 0 when it should return 1830. =SUMIFS({PURCHASING QTY EA}, {SHIP TO PLANT}, "HUBER HEIGHTS", {CONDUIT PURCHASING JDE CODE}, "RPVC0412200", {CONDUIT DELIVERY DATE}, >={CONDUIT INVENTORY DATE}, {CONDUIT…
-
Calculating duration with start/end day and time columns?
Hi, I'm looking for a way to calculate duration (preferably in hours) with start and end day/time columns (as pictured below). Currently entering duration manually and it's not ideal. The sheet in particular is for system downtime metric tracking and so knowing the date and time are obviously necessary. Date columns are…
-
Formula Help - Not sure what I am missing here
I'm working with a formula that should indicate the stage of an employee's onboarding based on a table. The stages are labeled as "PHASE COMPLETE," "PHASE NOT STARTED," and "PHASE IN PROCESS." However, I'm encountering a few issues: When everything is marked as complete, it returns an "invalid formula" error. If no phases…
-
How to get a formula to count only negatives or only positives
I have two formulas for a helper column to a metrics sheet. I made a column that counts the number of days until an order is due to arrive, and then there are two helper columns. the first needs to take those numbers and return a value based on the number. So if the order is set to arrive in 5 days, then the value would be…
