Indexing from different sheet with qualifier

Brent Allen

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.

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.

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

@Paul Newcome

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

@Paul Newcome

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

Thank you @Paul Newcome

Working now.

You said you had a parsing solution?

Paul Newcome

If you are wanting to parse your list down a single column, take a look at THIS SOLUTION that 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.