-
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…
-
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, "-", ""))
-
I can't get my Index Match Max formula to work- has error #UNPARSEABLE
Trying to find replica of excel's Xlookup formula. Here is my current formula that is not working: =INDEX({GP% TO PAY}, MATCH(MAX({HIGHEST TIER%})([% to GP Budget]@row, {HIGHEST TIER%}),0)) I am trying to pull in Employee's % to be paid on their bonus, based on the current sheet's % to GP Budget result. The helper sheet I…
-
Index Collect with an IF and a Join?
Good morning all I'm after help with a complex formula concept. FYI these are not live data sheets just moc ups. Data Base Sheet Metric Sheet Bellow is the formula im currently using to pull contracted orders across to a second Metric sheet. =IFERROR(INDEX(COLLECT({DATA BASE | Customer}, {DATA BASE | Start Date},…
-
Invalid Value on Index Collect
=IFERROR(INDEX(COLLECT({Checkbox Type Column "ID Finalized" on Smartsheet A}, {Text/Number Type Column "Reference" on Smartsheet A}, [Text/Number Column "ID" in Smartsheet B]@row), 1),"") The column for {Text/Number Type Column on Smartsheet A} called "Reference" could contain an ID like T1234 or a url https://abc.com…
-
Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
