-
INDEX COLLECT over multiple documents
I am looking for your help once again :) So the below formula works fine for me however I have other sheets that I also need to reference. I'm assuming I would need to add an AND or OR formula to the below however I'm not sure where or what structure the formula should be. And its to reference another 4 sheets, including…
-
Collect cross sheet formula help
I want to pull through the task name in one sheet where the check box at row is checked into another target sheet. This is what I though the formula would be but I get #UNPARSEABLE as the error. =INDEX(COLLECT({Task name}, {Stage check box}@row , 1)) Um, help please?
-
Multiple criteria for Index/Match (or collect?)
Hello, I am trying to return data from a cell in a reference sheet based on (2) criteria - one at the row, and one in the 1st row of that column. I have metrics sheets that accomplish a similar function but those are using sumif for calculations, whereas here I just need to pull in text. How do I create an index/match or…
-
Help for INDEX/COLLECT with multiple criteria
Hi everyone! I usually use INDEX/MATCH, but I am trying to "match" multiple criteria and found that it is better to use INDEX/COLLECT when dealing with multiple criteria. I keep returning "#INVALID VALUE" and am confused what I am missing… I have an "Audience" sheet my team is using with employees' respective name, ID…
-
INDEX COLLECT MIN (again)
Had a follow up question to my previous question located here. — I was using the original sheets, which I fixed with the solution from that thread, as a template. Today, I copied them over to essentially create the same system for a different LOB. I checked the box that updated all formulas to the new references. Now, in…
-
Lookup Formula Not working
I am attempting to do a lookup of a a Date (on the Deliverable Tracker, shown in screenshot #1). The lookup will use the Effective Start and End dates to find the appropriate range, then the end result will be the text value column at the far right. My formula shows a blank result, here is the formula:…
-
How to collect multiple Successor values?
Here's a link to my initial post which helped get me this far. RECAP: I use a combination of a Successor formula & Join/Collect in an attempt to report for my team the next tasks and assignee(s) in our waterfall workflow. I am able to successfully return the next assignee only when there is a single successor. I need to…
-
How to have each subsequent value that matches a criterion appear in its own cell in a column?
I'm trying to pull change order numbers that are assigned to one specific person in a source sheet appear as separate entries in one column in another sheet. I've gotten it to manually work with this formula: =INDEX(COLLECT({CO Number}, {Assigned PM}, "PM Name"), 1) But I have to manually adjust the number at the formula…
-
One of my criteria is one of many listed in a dropdown column and CONTAINS/HAS are eluding me.
Hello! I am working from a single "Master Sheet" and utilizing INDEX/COLLECT to pull the desired info onto separate sheets. I am utilizing "Feature", "Level" and "Product Line" as my criteria, however the "Product Line" is a multi-select dropdown column with anywhere from 1-10 values in it depending on the products usage.…
-
Pulling data from another sheet based on two criteria
Hello! I am having trouble getting this formula to work.. The goal is to pull the quantity of an item from a reference sheet to this sheet if the Spec #@row on this sheet matches an EAN on the reference sheet, and only if the Disposition value reads "Released". This is the formula I have cooked up, but get an…