-
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…
-
How can I get a multi-select dropdown column to populate with comma separation?
I have a two different columns that are populating differently, just using simple formulae: (I took out all the error checking to make it simple until I can get it to work) =[Name]@row, where [Name] is a Contact list, allowing multiple contacts per cell, and =[Department]@row, where [Department] is a Dropdown list, also…
-
I need help with formulas
I am working on formulas to get approvals in one of my sheets - But there are more than 2 options and I'm finding it a bit confusing… IF both directors APPROVE = Trade Off Accepted IF one director APPROVES and the other one is PENDING = Pending Acceptance (Would I need two of these?) IF both directors REJECT = Trade Off…
-
Parent rolling up data in Children rows
Green line is the parent row everything inbetween is children rows Column 30 is counting rows from N/A column that are not ticked, which are the rows im looking for. so 6 rows are active rows which are being used. out of the 6 rows, 4 rows have been ticked in appoinment concluded column i want to roll up the count of 4 in…