-
Cross reference match formula
I am looking to cross reference Sheet A to pull in Time slots from Sheet B. Criteria I need to have matching from Sheet A to pull from Sheet B to determine the slot number or multiple numbers. Start Time End Time There could be multiple Slot Numbers so it would need to be a multi option that I could use later to separate…
-
Multiple nested IF(AND) paired with INDEX/MATCH ? or other way ?
Hello to everyone. Im starting to go deeper into advanced formulas and sheet reference, but have some issue... I have a "customer activity" sheet, where for each line, there is 2 sets of variables (5 in "activity", 3 in "types"...) and a "Duration" column. I would like to compute the "Final Price", by referencing a "price…
-
Is there a way to pull cells that meet criteria into individual cells in another sheet?
I'm working on a multi-sheet solution for my PMO and have been using Index and Match to pull specific information from a metadata sheet into matching cells on other sheet using INDEX/MATCH. I've been using either the Change Order (CO)number or the Scope Change ID (Larger Project Identifier) for most of the solution, but…
-
Collect a Value from a Column if Today's Date Falls Between Two Dates
I am attempting what I thought was a simple formula to collect a value from the 'Week" column if today's date falls between a Start Date and an End Date. Here's what I have so far which returns an #INVALID OPERATION result: =COLLECT(Week:Week, Week:Week, AND(TODAY() >= [Start Date]:[Start Date], TODAY() <= [End Date]:[End…
-
Trying to collect data from a sheet where a person assigned is one of multiple contacts
Hi, I'm trying to revise a formula and am a bit stuck on how to do it. I would like to pull resource utilization information from my Project Plan into a Team sheet (and no, we don't have the Resource Management module or Control Center at my organization). Here is an example of the current formulas I'm working with:…
-
'Cleaning' Zoom Attendance Names to use Index/Collect
(We cannot use Zapier Integration). So, I created a Data Shuttle to pull the information from the Zoom attendance report. However, I need to Index/Collect how we designate the entity that they are associated with based on the person that attended, and we don't get the emails of outside parties from the meeting. People's…
-
#NO MATCH on MIN/COLLECT function
Hi, I am setting up two very similar sheets, with the same columns and similar data (each sheet is for a different date range). In both sheets, I have a column that uses a MIN/COLLECT function to detect duplicates when new rows are moved into the sheet. The formula works in one sheet, but in the other, it returns a #NO…
-
Adding One More Criteria to Working COLLECT Formula
I have a working formula where I need to add one more criteria to a COLLECT function but can't seem to get it working. Here is the current working formula: =MEDIAN(COLLECT({Platform Test - Submit Status to Intake Date}, {Platform Test - Quote Testing Completed Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1),…
-
Trying to get a list of distinct values across multiple columns based on multiple criteria
Hi, I am trying to list all the distinct values for the Year 1 through Year 4 columns but only for the rows the WBS row value is equal to WBS Target value and skip any blank cells. I have a helper column to index up to 10 distinct values (Screen shot below) Any help is greatly appreciated!
-
Help with INDEX/COLLECT with a MAX/COLLECT
I am attempting to create a few reference columns using an INDEX/COLLECT function and incorporating a MAX/COLLECT because there is a 1-Many relationship on the RTO Queue ID in the 2nd sheet so I would like to use the row with the MAX Fully Executed Date. Below is the formula I am using: =INDEX(COLLECT({IA Assigned}, {RTO…