-
Joining text "if" a criteria is met in multiple rows / columns
Hi, I'm trying to do something a bit complicated and hoping for some assistance. I am trying to join text from specific columns IF another column has specific verbiage in it (Campaign 1). Below is an image of the rows and the column with that verbiage. Do you know what would be the appropriate formula for this? I want to…
-
RANKEQ with multiple filtering criteria
I have been trying to rank my rows that are listed as "Tier 1" in the {Level} column in order of importance based on the following criteria: 1) Highest Rank: Rows with the oldest {R/O Create Date} AND BOTH {OOS} and {Warranty} columns are checked. 2) Second: Again, oldest R/O create date, with the {Warranty} column…
-
Why is my INDEX/COLLECT formula not working?
I have a very large contact database that has columns of information need to be updated periodically. I'd like to use a separate sheet with form as an activity log, where each new row contains the updated information for a specific contact in the database. To do this, each contact in the database has a unique ID. When…
-
How to do lookup for multi select dropdown cell to return results for all selected items in cell?
Hi I'm trying to do a lookup or index/match or whatever that can search all values in a dropdown list cell with multiple values and return multiple values. Sheet 1 screenshot (yellow highlight is where I want formula to be): Sheet 2 screenshot (contains table of lookup values): I'm trying formula from this question from…
-
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
-
Searching more than one worksheet to return value
I am utilizing multiple worksheets (one for each year) but have one sheet that has some consolidated information, and I would like for it to be able to pull from the appropriate year sheet. =IFERROR(INDEX(COLLECT({Quote Log 2024 Quote Status}, {Quote Log 2024 Quote #}, @cell = [Quote Number]@row), 1), "") I would like for…
-
I need collect to compare my criterion (@row data) with my criterion range (a referenced column)?
I've been working around collecting data from multiple sheets with help from this community, and I've run into another snag. I'm trying to pull data from a referenced column with a matching location name @row. It seems like a simple problem, but I keep getting #INCORRECT ARGUMENT SET. =COLLECT({2024 All Projects}, {2024…
-
How do I join specific cells from multiple sheets?
The question seems simpler than the outcome I'm hoping for. I need to combine distinct project types from multiple columns on multiple sheets. I know I need to use Join, Collect, Distinct, and potentially others, but I'm struggling with making it work the way I want it to. This is what I have: This sheet will be duplicated…
-
Index Collect with a Date Range Criterion
I am trying to utilize an index collect formula that will cross-reference another sheet. Some of the reference criteria are numbers but two criteria need to narrow the options to a specific date range (i.e. >= DATE1 and < =DATE2). These columns are in date format. The data I am trying to bring back into the cell is a name…
-
Removing blanks from Join Collect output
I have a sheet that is pulling in data for metrics from another sheet. Using Join(Collect() I get the output I want but, it does include the blank cells from the range. I would like to not have them included…. =JOIN(COLLECT({PECR Range 6}, {PECR 2}, Region1), SUBSTITUTE([Column6]1, "-", ""))
