-
IfError, Average, Collect, and Round Up to nearest whole formula
I have this formula =IFERROR(AVG(COLLECT([Time to Complete]:[Time to Complete], [1. Assigned to]:[1. Assigned to], "W - Water")), "") How can I make the result round up to the nearest whole number?
-
INDEX(COLLECT - #Invalid Value Error
Good afternoon everyone! Looking for some help with INDEX(Collect. I have a main sheet set up and also have created a helper sheet. Once the install date on the main sheet is changed, automation copies that row to the helper sheet. I want to see on my main sheet the previous install date as well. This I've figured out. I…
-
Help with Join+Collect Formula
I need to reference another sheet to join and collect the skills of employees. The yellow is my desired outcome, and I'm using the following formula. Formula: =JOIN(COLLECT({Detailed Resource Data Master Range Skills}, {Detailed Resource Data Master Range Emp ID}, [Employee ID@row), ",") I'm not getting any errors, the…
-
Join/Collect (Distinct?) Help with Combining Separate Line Items Based on Unique Values
So I'm having an issue (lack of understanding) on how to use join/collect to turn a sheet like this: Into one that combines Qty/Supplies into one line item based on data from multiple other columns. End result like this: I would like to get it so that Supplies/Qty get merged when Department, ID Number, Drop Off Location,…
-
Complex Lookup
I have a main table that, given the team(s) involved in a project, needs to look up the contact info for members of all of those teams. ([Teams Involved] is a multiselect dropdown, and [Members of Involved Teams] is a contact list where multiple contacts are allowed.) It is to get the list of team members from a lookup…
-
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:…