-
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…
-
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
-
INDEX MATCH Formula help
Hi I'm looking for help with a formula. I've gotten some of it written but I'm missing something. I have a "Mastersheet" where my form responses are held. Then depending on the form originator the row is copied to a new sheet. The new sheet will be where tasks are worked on and completed. I have a "Record Closed Out?"…
-
Index collect formula question
Hello I need some assistance with my index collect function, In one cell i'd have =IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event], [Column23]:[Column23], [Week #]@row, Email:Email, "Person 1"), 1), "") In another cell i'd have =IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event],…
