Index Collect with Contains or Has for Multi Drop Down & MultiSheet

paige_pj

We are trying to create Individual Profiles that pull a date from another sheet based on 2 criteria where 1 of them is a multidrop down.

We are using where we put the Client Name in a cell to use as where the text string comes from not have to edit the formula each time with the name of the client.

So, we have sheet one where all Client Contacts go including 1 type of contact that is a group meeting.

I can't send screen shots b/c it would show information I can't. So, trying to do this through explaining.

Client Contact Sheet:

Has Date, Checkbox for most recent contact, and Client Name. The Client Name column sometimes has multiple clients in it. It works when there are not multiple names in the cell. But, we want to log the group meetings together.

The Profile has the cell where we put the client name to create a spot that the formula knows what to look for instead of formulas with "text strings".

We've tried MANY formulas that go between Incorrect Argument and Unparseable

(INDEX(COLLECT({Date}, {MostRecent}, CONTAINS($[ColumnwithClientName]$1, {ClientName}, 1), 1)

(INDEX(COLLECT({Date}, {MostRecent}, CONTAINS($[ColumnwithClientName]$1, @cell), {ClientName}, 1), 1)

(INDEX(COLLECT({Date}, {MostRecent}, 1, {ClientName}, CONTAINS($[ColumnwithClientName]$1, @cell), {ClientName}, 1))

(INDEX(COLLECT({Date}, {MostRecent}, 1, {ClientName}, CONTAINS($[ColumnwithClientName]$1, {ClientName}, 1))

From the Function pages, it looks like this should work.

INDEX(COLLECT({Column with Data you want}, {Criterion 1 Range}, Criterion 1, {Criterion 2 range}, CONTAINS($[CellwithInformation]$1, {ContainsSearchColumn}), Criterion 2))

Where:

Criterion 1 = 1 b/c it's a check box and we want it to be checked

Criterion 2 = 1 - Unsure on if this is right? Should that not be 1 to say yes we want you to find it?

I'm also unsure if when you use a $[CellwithInformation]$1 - you need the @cell.

Shanky Paul

Did you tried the below one?

=INDEX(COLLECT({Date}, {MostRecent}, 1, {ClientName}, CONTAINS($[ColumnwithClientName]$1, @cell)), 1)

I hope this formula should help you pull the correct date even when multiple client names are in a single cell.

Shanky Paul

Did you tried the below one?

=INDEX(COLLECT({Date}, {MostRecent}, 1, {ClientName}, CONTAINS($[ColumnwithClientName]$1, @cell)), 1)

I hope this formula should help you pull the correct date even when multiple client names are in a single cell.

paige_pj

Yes! I had tried that with an incorrect placement of the )!

I had done @cell), 1))…but the ) needed to close the COLLECT! Thank you!