Dropdown List Alternative with Automatic List Update and Dynamic List Features
jmyzk_cloudsmart_jp
✭✭✭✭✭
There have been many requests for improvements to the Dropdown list from Smartsheet Community.
The two most typical requests are
- Automatic updating of the list@Justin Mauzy@Taylor Compton - NOAA Federal
- Dynamically changing the list of the following selections based on previous selections@Ian Cooper@Mehran Bagherian
Regarding the firstautomatic updating of the list,users want a master sheet of dropdown lists, so changing the list will change the options on the target sheet's dropdown lists. Since this functionality is unavailable, the user must manually update the list.
As for the second,dynamically changing the list of the following selections based on previous selections,some mimic dynamic or dependent dropdown lists, usingform logicto display a specific column of choices based on the previous selections, preparing columns for the options in advance. However, this method also requires manual preparation of the columns of choices and works only for the form.
To solve this problem, we have devised a workaround that displaysa list of two-digit numeric IDs and dropdown list items in a cellso that users can enter the two-digit numeric IDs corresponding to a list item.
Although it is not as easy as a dropdown list, where you can click on a choice and the selection is complete, our method only requires you to enter a two-digit number, so it is not much of a hassle. You can prepare the two-digit number choices and have the user click on them.
To try this solution, please refer to the sample dashboard in the following link.
For more information, please refer to our homepage article and Youtube video in the following links.
Comments
-
jmyzk_cloudsmart_jp ✭✭✭✭✭
Here is another "Dropdown List Alternative"
I came up with this alternative answering Jadon's "Formula as an alternative to Dynamic dropdown menus?" question.
With this method, you can narrow down candidate pairs of the "Code: Value" list by inputting part of the list's value.
Categories
2023-06-25T05:03:02.000+00:00","expand":["all","-body"],"excludeHiddenCategories":true},"discussions":[{"discussionID":107989,"type":"discussion","name":"Meet David Tutwiler, our July Member Spotlight! 🎉","excerpt":"@David Tutwiler is everywhere in the Smartsheet Community, on platform and off. Not only did he just graduate from the two year Smartsheet Overachiever program, but he was nominated this month by his peers to be our Member Spotlight! David is currently the Director of Information Technology at SWC, residing in Sweet Home…","snippet":"@David Tutwiler is everywhere in the Smartsheet Community, on platform and off. Not only did he just graduate from the two year Smartsheet Overachiever program, but he was…","categoryID":346,"dateInserted":"2023-07-24T18:11:32+00:00","dateUpdated":"2023-07-24T18:51:38+00:00","dateLastComment":"2023-07-24T18:51:35+00:00","insertUserID":126614,"insertUser":{"userID":126614,"name":"Alison Clancy","title":"Community Engagement Manager II","url":"https:\/\/community.smartsheet.com\/profile\/Alison%20Clancy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nEFeH-a5APo!7Idee8avSzo!8w93-Gb3myE","dateLastActive":"2023-07-24T22:46:57+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"updateUserID":126614,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-25T04:29:00+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":true,"pinLocation":"recent","closed":false,"sink":false,"countComments":4,"countViews":36,"score":2,"hot":3380449990,"url":"https:\/\/community.smartsheet.com\/discussion\/107989\/meet-david-tutwiler-our-july-member-spotlight","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107989\/meet-david-tutwiler-our-july-member-spotlight","format":"Rich","tagIDs":[397],"lastPost":{"discussionID":107989,"commentID":386711,"name":"Re: Meet David Tutwiler, our July Member Spotlight! 🎉","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386711#Comment_386711","dateInserted":"2023-07-24T18:51:35+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-25T04:29:00+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Show & Tell","url":"https:\/\/community.smartsheet.com\/categories\/show-%26-tell"}],"groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/P2P1YF7YLVA4\/david-tutwiler.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"david-tutwiler.jpg"},"attributes":{},"status":{"statusID":0,"name":"None","state":"open","recordType":"discussion","recordSubType":"discussion"},"bookmarked":false,"unread":false,"category":{"categoryID":346,"name":"Show & Tell","url":"https:\/\/community.smartsheet.com\/categories\/show-%26-tell","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":2}],"tags":[{"tagID":397,"urlcode":"smartsheetmember","name":"Member Spotlight"}]},{"discussionID":107974,"type":"question","name":"Column Headings and Linking","excerpt":"Hi Guys A quick question (if there is such a thing); when adding a link from one sheet to a cell in another sheet do the column headings have to be the same? My assumption would be they don't, I imagine the column name 'internally' is irrelevant and there is an underlying system generated reference, otherwise changing a…","snippet":"Hi Guys A quick question (if there is such a thing); when adding a link from one sheet to a cell in another sheet do the column headings have to be the same? My assumption would…","categoryID":346,"dateInserted":"2023-07-24T14:20:13+00:00","dateUpdated":null,"dateLastComment":"2023-07-24T15:03:45+00:00","insertUserID":163092,"insertUser":{"userID":163092,"name":"Terry P","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Terry%20P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DmPeg7qW1bw!pGlRKeSnojo!WQnClWI2btr","dateLastActive":"2023-07-24T17:01:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-24T20:52:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":22,"score":null,"hot":3380420038,"url":"https:\/\/community.smartsheet.com\/discussion\/107974\/column-headings-and-linking","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107974\/column-headings-and-linking","format":"Rich","tagIDs":[334,472],"lastPost":{"discussionID":107974,"commentID":386659,"name":"Re: Column Headings and Linking","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386659#Comment_386659","dateInserted":"2023-07-24T15:03:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-24T20:52:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Show & Tell","url":"https:\/\/community.smartsheet.com\/categories\/show-%26-tell"}],"groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2023-07-24T15:03:45+00:00","acceptedAnswers":[]}},"status":{"statusID":2,"name":"Answered","state":"open","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":346,"name":"Show & Tell","url":"https:\/\/community.smartsheet.com\/categories\/show-%26-tell","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":[{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}]},{"discussionID":107803,"type":"discussion","name":"Parallel applicant tracking system in Smartsheet","excerpt":"I love how robust Smartsheet is and what wonders it can do. We recently took a challenge from the HR team and built a parallel fully functional applicant tracking system in Smartsheet, which replaced their legacy ATS. Check out the details here - https:\/\/www.linkedin.com\/feed\/update\/urn:li:activity:7087444898932498433","snippet":"I love how robust Smartsheet is and what wonders it can do. We recently took a challenge from the HR team and built a parallel fully functional applicant tracking system in…","categoryID":346,"dateInserted":"2023-07-19T15:42:12+00:00","dateUpdated":"2023-07-19T15:42:35+00:00","dateLastComment":"2023-07-19T15:42:12+00:00","insertUserID":115705,"insertUser":{"userID":115705,"name":"Amit Wadhwani","title":"Community Champion","url":"https:\/\/community.smartsheet.com\/profile\/Amit%20Wadhwani","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/A7WE6VG5HIXY\/nCDYLGC9O5JJP.jpg","dateLastActive":"2023-07-20T05:37:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":115705,"lastUserID":115705,"lastUser":{"userID":115705,"name":"Amit Wadhwani","title":"Community Champion","url":"https:\/\/community.smartsheet.com\/profile\/Amit%20Wadhwani","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/A7WE6VG5HIXY\/nCDYLGC9O5JJP.jpg","dateLastActive":"2023-07-20T05:37:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":0,"countViews":21,"score":1,"hot":3379562987,"url":"https:\/\/community.smartsheet.com\/discussion\/107803\/parallel-applicant-tracking-system-in-smartsheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107803\/parallel-applicant-tracking-system-in-smartsheet","format":"Rich","lastPost":{"discussionID":107803,"name":"Parallel applicant tracking system in Smartsheet","url":"https:\/\/community.smartsheet.com\/discussion\/107803\/parallel-applicant-tracking-system-in-smartsheet","dateInserted":"2023-07-19T15:42:12+00:00","insertUserID":115705,"insertUser":{"userID":115705,"name":"Amit Wadhwani","title":"Community Champion","url":"https:\/\/community.smartsheet.com\/profile\/Amit%20Wadhwani","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/A7WE6VG5HIXY\/nCDYLGC9O5JJP.jpg","dateLastActive":"2023-07-20T05:37:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Show & Tell","url":"https:\/\/community.smartsheet.com\/categories\/show-%26-tell"}],"groupID":null,"statusID":0,"attributes":{},"status":{"statusID":0,"name":"None","state":"open","recordType":"discussion","recordSubType":"discussion"},"bookmarked":false,"unread":false,"category":{"categoryID":346,"name":"Show & Tell","url":"https:\/\/community.smartsheet.com\/categories\/show-%26-tell","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":1},{"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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=346&includeChildCategories=1&dateInserted=%3E2023-06-25T05%3A03%3A02%2B00%3A00&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser","prevURL":null,"currentPage":1,"total":21,"limit":3},"title":"Trending in Show & Tell","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
![https:\/\/us.v-cdn.net\/6031209\/uploads\/204PSLPS8K8N\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png<\/a><\/p>Just Get Code From Description by Index Match<\/strong><\/p>Get Code from a table referencing CODE by INDEX and MATCH based on Description.<\/p>=IFERROR(INDEX({Code}, MATCH(Description@row, {Description}, 0), 1), "N\/A")<\/p>Get the Code if part of the description match list.<\/strong><\/p>Get CODE if part of the Description matches the Description of the referenced table.<\/p>=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell)), 1), "N\/A")<\/p>Get Code if more than % description matches the list<\/strong><\/p>Part of the above is to get the CODE if the match is more than the % specified in the Field of the Sheet Summary<\/p>=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell), {Description}, LEN(Description@row) \/ LEN(@cell) > [%]#), 1 ), "N\/A")<\/p>Next, here is an improved version of my recommended method of displaying candidate dropdown lists from a separate sheet<\/strong><\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/VY7867W46133\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png<\/a><\/p>Candidate based on the part of Description<\/strong><\/p>If a part of the Description matches the Description of the referenced table, the table's Code and the formula display the pairs as a dropdown list<\/p>=JOIN(COLLECT({Menu}, {Description}, CONTAINS(Description@row, @cell)), CHAR(10))<\/p>Candidate based on the part of Description: Ignore Case<\/strong><\/p>Same as above, but case-insensitive.<\/p>=JOIN(COLLECT({Menu}, {Description}, CONTAINS(LOWER(Description@row), LOWER(@cell)))), CHAR(10))<\/p>Please take a look at a published dashboard demo.<\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/FNO3QG3SA0IZ\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png<\/a><\/p>https:\/\/app.smartsheet.com\/b\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74<\/a><\/p>I have been thinking of various ways to display a combination of numbers and dropdown listings from a dropdown master sheet, and now, in answering your question, I have come up with a way to display a listing if, for example, I put in part of the name of a person or product.<\/strong><\/p>The problem with displaying a combination of numbers and dropdown lists from a dropdown master sheet is that it is unwieldy when the list is long. The nice thing about the method, where if you put in part of the name of a person or product, the list will appear, is that the candidates are bound, making entering the Code easier.<\/p>It is a kind of dynamic dropdown list, so to speak. I think this is a practical solution.<\/p>In addition to this, I also present an alternative to the Dynamic Dropdown that automatically creates a table of lists from a master sheet.<\/p>It is somewhat complicated, so I won't force you to go "still new to Smartsheet," but I will provide a link for your reference.<\/p>https:\/\/community.smartsheet.com\/discussion\/105722\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features<\/a><\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Jadon Meador\",\"userID\":161730}}},{\"insert\":\" \\nAlternative to Dynamic dropdown is a subject I am very interested in.\\nI may have answered your question incorrectly this time, but I have come up with some solutions based on your shared data.\"},{\"insert\":{\"emoji\":{\"emojiChar\":\"😀\"}}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/204PSLPS8K8N\\\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/204PSLPS8K8N\\\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png\"}},{\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Just Get Code From Description by Index Match\"},{\"insert\":\"\\nGet Code from a table referencing CODE by INDEX and MATCH based on Description.\\n=IFERROR(INDEX({Code}, MATCH(Description@row, {Description}, 0), 1), \\\"N\\\/A\\\")\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Get the Code if part of the description match list.\"},{\"insert\":\"\\nGet CODE if part of the Description matches the Description of the referenced table.\\n=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell)), 1), \\\"N\\\/A\\\")\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Get Code if more than % description matches the list\"},{\"insert\":\"\\nPart of the above is to get the CODE if the match is more than the % specified in the Field of the Sheet Summary\\n=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell), {Description}, LEN(Description@row) \\\/ LEN(@cell) > [%]#), 1 ), \\\"N\\\/A\\\")\\n\\nNext, here is an improved version of my recommended \"},{\"attributes\":{\"bold\":true},\"insert\":\"method of displaying candidate dropdown lists from a separate sheet\"},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VY7867W46133\\\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VY7867W46133\\\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Candidate based on the part of Description\"},{\"insert\":\"\\nIf a part of the Description matches the Description of the referenced table, the table's Code and the formula display the pairs as a dropdown list\\n=JOIN(COLLECT({Menu}, {Description}, CONTAINS(Description@row, @cell)), CHAR(10))\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Candidate based on the part of Description: Ignore Case\"},{\"insert\":\"\\nSame as above, but case-insensitive.\\n=JOIN(COLLECT({Menu}, {Description}, CONTAINS(LOWER(Description@row), LOWER(@cell)))), CHAR(10))\\nPlease take a look at a published dashboard demo.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/FNO3QG3SA0IZ\\\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/FNO3QG3SA0IZ\\\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74\",\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nI have been thinking of various ways to display a combination of numbers and dropdown listings from a dropdown master sheet, and now, in answering your question, I have come up with \"},{\"attributes\":{\"bold\":true},\"insert\":\"a way to display a listing if, for example, I put in part of the name of a person or product.\"},{\"insert\":\"\\nThe problem with displaying a combination of numbers and dropdown lists from a dropdown master sheet is that it is unwieldy when the list is long. The nice thing about the method, where if you put in part of the name of a person or product, the list will appear, is that the candidates are bound, making entering the Code easier.\\nIt is a kind of dynamic dropdown list, so to speak. I think this is a practical solution.\\nIn addition to this, I also present an alternative to the Dynamic Dropdown that automatically creates a table of lists from a master sheet.\\nIt is somewhat complicated, so I won't force you to go \\\"still new to Smartsheet,\\\" but I will provide a link for your reference.\\n\"},{\"insert\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/105722\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\",\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/105722\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-06-14T03:33:40+00:00","insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"jmyzk","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-07-24T02:45:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380420#Comment_380420","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380420#Comment_380420](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/204PSLPS8K8N\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/VY7867W46133\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png<\/a><\/p>Candidate based on the part of Description<\/strong><\/p>If a part of the Description matches the Description of the referenced table, the table's Code and the formula display the pairs as a dropdown list<\/p>=JOIN(COLLECT({Menu}, {Description}, CONTAINS(Description@row, @cell)), CHAR(10))<\/p>Candidate based on the part of Description: Ignore Case<\/strong><\/p>Same as above, but case-insensitive.<\/p>=JOIN(COLLECT({Menu}, {Description}, CONTAINS(LOWER(Description@row), LOWER(@cell)))), CHAR(10))<\/p>Please take a look at a published dashboard demo.<\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/FNO3QG3SA0IZ\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png<\/a><\/p>https:\/\/app.smartsheet.com\/b\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74<\/a><\/p>I have been thinking of various ways to display a combination of numbers and dropdown listings from a dropdown master sheet, and now, in answering your question, I have come up with a way to display a listing if, for example, I put in part of the name of a person or product.<\/strong><\/p>The problem with displaying a combination of numbers and dropdown lists from a dropdown master sheet is that it is unwieldy when the list is long. The nice thing about the method, where if you put in part of the name of a person or product, the list will appear, is that the candidates are bound, making entering the Code easier.<\/p>It is a kind of dynamic dropdown list, so to speak. I think this is a practical solution.<\/p>In addition to this, I also present an alternative to the Dynamic Dropdown that automatically creates a table of lists from a master sheet.<\/p>It is somewhat complicated, so I won't force you to go "still new to Smartsheet," but I will provide a link for your reference.<\/p>https:\/\/community.smartsheet.com\/discussion\/105722\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features<\/a><\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Jadon Meador\",\"userID\":161730}}},{\"insert\":\" \\nAlternative to Dynamic dropdown is a subject I am very interested in.\\nI may have answered your question incorrectly this time, but I have come up with some solutions based on your shared data.\"},{\"insert\":{\"emoji\":{\"emojiChar\":\"😀\"}}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/204PSLPS8K8N\\\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/204PSLPS8K8N\\\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png\"}},{\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Just Get Code From Description by Index Match\"},{\"insert\":\"\\nGet Code from a table referencing CODE by INDEX and MATCH based on Description.\\n=IFERROR(INDEX({Code}, MATCH(Description@row, {Description}, 0), 1), \\\"N\\\/A\\\")\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Get the Code if part of the description match list.\"},{\"insert\":\"\\nGet CODE if part of the Description matches the Description of the referenced table.\\n=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell)), 1), \\\"N\\\/A\\\")\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Get Code if more than % description matches the list\"},{\"insert\":\"\\nPart of the above is to get the CODE if the match is more than the % specified in the Field of the Sheet Summary\\n=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell), {Description}, LEN(Description@row) \\\/ LEN(@cell) > [%]#), 1 ), \\\"N\\\/A\\\")\\n\\nNext, here is an improved version of my recommended \"},{\"attributes\":{\"bold\":true},\"insert\":\"method of displaying candidate dropdown lists from a separate sheet\"},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VY7867W46133\\\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VY7867W46133\\\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Candidate based on the part of Description\"},{\"insert\":\"\\nIf a part of the Description matches the Description of the referenced table, the table's Code and the formula display the pairs as a dropdown list\\n=JOIN(COLLECT({Menu}, {Description}, CONTAINS(Description@row, @cell)), CHAR(10))\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Candidate based on the part of Description: Ignore Case\"},{\"insert\":\"\\nSame as above, but case-insensitive.\\n=JOIN(COLLECT({Menu}, {Description}, CONTAINS(LOWER(Description@row), LOWER(@cell)))), CHAR(10))\\nPlease take a look at a published dashboard demo.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/FNO3QG3SA0IZ\\\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/FNO3QG3SA0IZ\\\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74\",\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nI have been thinking of various ways to display a combination of numbers and dropdown listings from a dropdown master sheet, and now, in answering your question, I have come up with \"},{\"attributes\":{\"bold\":true},\"insert\":\"a way to display a listing if, for example, I put in part of the name of a person or product.\"},{\"insert\":\"\\nThe problem with displaying a combination of numbers and dropdown lists from a dropdown master sheet is that it is unwieldy when the list is long. The nice thing about the method, where if you put in part of the name of a person or product, the list will appear, is that the candidates are bound, making entering the Code easier.\\nIt is a kind of dynamic dropdown list, so to speak. I think this is a practical solution.\\nIn addition to this, I also present an alternative to the Dynamic Dropdown that automatically creates a table of lists from a master sheet.\\nIt is somewhat complicated, so I won't force you to go \\\"still new to Smartsheet,\\\" but I will provide a link for your reference.\\n\"},{\"insert\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/105722\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\",\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/105722\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-06-14T03:33:40+00:00","insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"jmyzk","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-07-24T02:45:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380420#Comment_380420","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380420#Comment_380420](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/VY7867W46133\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/FNO3QG3SA0IZ\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png<\/a><\/p>https:\/\/app.smartsheet.com\/b\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74<\/a><\/p>I have been thinking of various ways to display a combination of numbers and dropdown listings from a dropdown master sheet, and now, in answering your question, I have come up with a way to display a listing if, for example, I put in part of the name of a person or product.<\/strong><\/p>The problem with displaying a combination of numbers and dropdown lists from a dropdown master sheet is that it is unwieldy when the list is long. The nice thing about the method, where if you put in part of the name of a person or product, the list will appear, is that the candidates are bound, making entering the Code easier.<\/p>It is a kind of dynamic dropdown list, so to speak. I think this is a practical solution.<\/p>In addition to this, I also present an alternative to the Dynamic Dropdown that automatically creates a table of lists from a master sheet.<\/p>It is somewhat complicated, so I won't force you to go "still new to Smartsheet," but I will provide a link for your reference.<\/p>https:\/\/community.smartsheet.com\/discussion\/105722\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features<\/a><\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Jadon Meador\",\"userID\":161730}}},{\"insert\":\" \\nAlternative to Dynamic dropdown is a subject I am very interested in.\\nI may have answered your question incorrectly this time, but I have come up with some solutions based on your shared data.\"},{\"insert\":{\"emoji\":{\"emojiChar\":\"😀\"}}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/204PSLPS8K8N\\\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/204PSLPS8K8N\\\/formula-as-alternative-to-dynamic-dropdown-menus-simple-index-match.png\"}},{\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Just Get Code From Description by Index Match\"},{\"insert\":\"\\nGet Code from a table referencing CODE by INDEX and MATCH based on Description.\\n=IFERROR(INDEX({Code}, MATCH(Description@row, {Description}, 0), 1), \\\"N\\\/A\\\")\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Get the Code if part of the description match list.\"},{\"insert\":\"\\nGet CODE if part of the Description matches the Description of the referenced table.\\n=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell)), 1), \\\"N\\\/A\\\")\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Get Code if more than % description matches the list\"},{\"insert\":\"\\nPart of the above is to get the CODE if the match is more than the % specified in the Field of the Sheet Summary\\n=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell), {Description}, LEN(Description@row) \\\/ LEN(@cell) > [%]#), 1 ), \\\"N\\\/A\\\")\\n\\nNext, here is an improved version of my recommended \"},{\"attributes\":{\"bold\":true},\"insert\":\"method of displaying candidate dropdown lists from a separate sheet\"},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VY7867W46133\\\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VY7867W46133\\\/formula-as-alternative-to-dynamic-dropdown-menus-show-list-based-on-input.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Candidate based on the part of Description\"},{\"insert\":\"\\nIf a part of the Description matches the Description of the referenced table, the table's Code and the formula display the pairs as a dropdown list\\n=JOIN(COLLECT({Menu}, {Description}, CONTAINS(Description@row, @cell)), CHAR(10))\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Candidate based on the part of Description: Ignore Case\"},{\"insert\":\"\\nSame as above, but case-insensitive.\\n=JOIN(COLLECT({Menu}, {Description}, CONTAINS(LOWER(Description@row), LOWER(@cell)))), CHAR(10))\\nPlease take a look at a published dashboard demo.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/FNO3QG3SA0IZ\\\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/FNO3QG3SA0IZ\\\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74\",\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bb7e2067fe42460da83062cb15777d74\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nI have been thinking of various ways to display a combination of numbers and dropdown listings from a dropdown master sheet, and now, in answering your question, I have come up with \"},{\"attributes\":{\"bold\":true},\"insert\":\"a way to display a listing if, for example, I put in part of the name of a person or product.\"},{\"insert\":\"\\nThe problem with displaying a combination of numbers and dropdown lists from a dropdown master sheet is that it is unwieldy when the list is long. The nice thing about the method, where if you put in part of the name of a person or product, the list will appear, is that the candidates are bound, making entering the Code easier.\\nIt is a kind of dynamic dropdown list, so to speak. I think this is a practical solution.\\nIn addition to this, I also present an alternative to the Dynamic Dropdown that automatically creates a table of lists from a master sheet.\\nIt is somewhat complicated, so I won't force you to go \\\"still new to Smartsheet,\\\" but I will provide a link for your reference.\\n\"},{\"insert\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/105722\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\",\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/105722\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-06-14T03:33:40+00:00","insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"jmyzk","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-07-24T02:45:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380420#Comment_380420","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380420#Comment_380420](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/FNO3QG3SA0IZ\/formula-as-alternative-to-dynamic-dropdown-menus-demo-db.png\"){kind=link}