Combining MATCH and COLLECT
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
Best Answer
-
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
(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.
(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.
Answers
-
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
(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.
(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.
-
Hi
Thankyou so very much for giving such a detailed answer. Really appreciate it. This will come in great use.
regards
Reema
-
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!