Indexing from different sheet with qualifier
Hi,
I'm trying to pull certain cells into a new sheet, but having issues with the syntax.
I'm referencing two columns, Test Name and Status, and trying to pull in all Test Names where the status is 'Live'.
My current formula results in #UNPARSABLE
=INDEX({Opt Roadmap1},COLLECT([Test Name]:[Test Name],MATCH("Live",[Status]:[Status])))
I know it would be simple to create a Report with a filter, but leadership is adamant on using Card View.
Best Answer
-
Paul Newcome ✭✭✭✭✭✭
You then use a delimiter that would not be present in any of the collected cells which allows you to parse. There are a few parsing solutions out there in the Community. If you can't find one or need help making one work, feel free to let me know, and we will see what we can figure out. I personally have a couple of parsing solutions already built. I would just have to dig through notes to find them.
Answers
-
Paul Newcome ✭✭✭✭✭✭
You would need to use a JOIN/COLLECT similar to...
=JOIN(COLLECT({Opt Roadmap Test Name Column}, {Opt Roadmap Status Column}, "Live"), "/\")
And then parse out the list based on the /\ delimiter into separate cells.
-
Brent Allen ✭✭
Thanks, I probably wasn't clear in my explanation.
I actually don't need to include the status, I'm using it more like a where clause.
I just need to pull in the Test Name from Sheet 1 (where status is live), into sheet 2
-
Paul Newcome ✭✭✭✭✭✭
Yes. It won't pull the status. It will pull every cell from the test name column where the status column says "live".
-
Paul Newcome ✭✭✭✭✭✭
You then use a delimiter that would not be present in any of the collected cells which allows you to parse. There are a few parsing solutions out there in the Community. If you can't find one or need help making one work, feel free to let me know, and we will see what we can figure out. I personally have a couple of parsing solutions already built. I would just have to dig through notes to find them.
-
Brent Allen ✭✭
Thanks@Paul Newcome
-
Brent Allen ✭✭
Using all the assistance from above, still getting #UNPARSEABLE. Including screenshot of code for direct reference
-
Paul Newcome ✭✭✭✭✭✭
You need to remove the square brackets from around "Live".
-
Paul Newcome ✭✭✭✭✭✭
You will also need to move one of the closing parenthesis to before the final comma to close out the COLLECT function before you specify your delimiter.
-
Brent Allen ✭✭
-
Paul Newcome ✭✭✭✭✭✭
If you are wanting to parse your list down a single column, take a look atTHIS SOLUTIONthat you should be able to adapt to your specific needs.
The linked solution uses a comma "," as the delimiter, so you will want to update everything so that either your JOIN formulas uses a comma or the parsing solution looks for "/\" instead.
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/73702/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"