Combining MATCH and COLLECT

RSethi

hi

Apologies in advance, I am not an expert smartsheet user, still learning.

I have a Sheet 1 where I have two columns Instructor_ID and Name

Then on sheet 2 I would like to auto populate the Name when I type in the Instructor_ID.

Something like COLLECT the Name from Sheet 1 where it MATCHES the Instructor_ID on sheet 2 with Instructor_ID on Sheet 1 and return the Name from Sheet 1.

Makes sense? Can this be done

jmyzk_cloudsmart_jp

Hi @RSethi

When you input part of the Instructor_id, the solution below shows candidates with [Name]s with [No.] for selection and the whole Instructor_id.

When you input the [No.] from the candidates, a formula will populate the selected Name.

Sheet 1 (Data Sheet)

Add two helper columns, [No.] and [No. & Name & ID]

[No. & Name & ID] =[No.]@row + ": " + Name@row + " " + [Instructor_ID]@row

https://app.smartsheet.com/b/publish?EQBCT=5d6fbcc92cda441ba257ec02a8192433 (Link to the editable published sheet)

Sheet 2 (Look up sheet)

The first formula for [Candidates] uses your idea to use the COLLECT function.

[Candidates] =JOIN(COLLECT({Sheet 1 Range : No. & Name & ID}, {Sheet 1 Range : Instructor_ID}, CONTAINS([Part of Instructor_ID]@row, @cell)), CHAR(10))

[Name] =IFERROR(INDEX({Sheet 1 Range : Name}, Choice@row), "")

The COLLECT functions criterion1, CONTAINS([Part of Instructor_ID]@row, @cell), checks if [Part of Instructor_ID]@row is contained or not for each value in the {Sheet 1 Range : Instructor_ID} range.

https://app.smartsheet.com/b/publish?EQBCT=e2b851578322473aaf54b51be8c65ee3 (Link to the editable published sheet)

The above solution uses the technique I explained in the following discussion. In short, the method creates a dropdown list alternative whose list is dynamically updated based on a data sheet.

https://community.smartsheet.com/discussion/105722/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features

jmyzk_cloudsmart_jp

Hi @RSethi

When you input part of the Instructor_id, the solution below shows candidates with [Name]s with [No.] for selection and the whole Instructor_id.

When you input the [No.] from the candidates, a formula will populate the selected Name.

Sheet 1 (Data Sheet)

Add two helper columns, [No.] and [No. & Name & ID]

[No. & Name & ID] =[No.]@row + ": " + Name@row + " " + [Instructor_ID]@row

https://app.smartsheet.com/b/publish?EQBCT=5d6fbcc92cda441ba257ec02a8192433 (Link to the editable published sheet)

Sheet 2 (Look up sheet)

The first formula for [Candidates] uses your idea to use the COLLECT function.

[Candidates] =JOIN(COLLECT({Sheet 1 Range : No. & Name & ID}, {Sheet 1 Range : Instructor_ID}, CONTAINS([Part of Instructor_ID]@row, @cell)), CHAR(10))

[Name] =IFERROR(INDEX({Sheet 1 Range : Name}, Choice@row), "")

The COLLECT functions criterion1, CONTAINS([Part of Instructor_ID]@row, @cell), checks if [Part of Instructor_ID]@row is contained or not for each value in the {Sheet 1 Range : Instructor_ID} range.

https://app.smartsheet.com/b/publish?EQBCT=e2b851578322473aaf54b51be8c65ee3 (Link to the editable published sheet)

The above solution uses the technique I explained in the following discussion. In short, the method creates a dropdown list alternative whose list is dynamically updated based on a data sheet.

https://community.smartsheet.com/discussion/105722/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features

RSethi

Hi

Thankyou so very much for giving such a detailed answer. Really appreciate it. This will come in great use.

regards

Reema

jmyzk_cloudsmart_jp

Happy to help!😁