Formula as alternative to Dynamic dropdown menus?
Hello,
I have seen in previous discussions that dynamic dropdown menus are not available without a third party app or Data Shuffle. Neither of those are options right now, so I am trying to do the next best thing.
My situation is this: I have two sheets , the first sheet is a a project specific sheet that has the task codes associated with that project specifically. See below.
The second sheet is my target sheet where I want the values returned. I have an index match formula for the job #, so when the job name is entered, the job # automatically populates. See below.
This is where I am running into issues on the second sheet. I would like to enter the task code description, and it automatically populate the task code (using the index match formula)ifthat task code is associated with the job number that was just entered. If it not associated with that job number, I would like it to return "NA"
I am still new to Smartsheet and the formulas, so please let me know if I am way off base here or if there is an easier solution.
This is the formula I am trying to work with, it is returning a Invalid Data Type Error. =IF(AND([Job #]@row, INDEX({Task Code Number}, MATCH(Description@row, {Task Code Title}, 0)) = INDEX({23-0080 Foreman Budget Range 1}, MATCH(Description@row, {Task Code Title}, 0))), INDEX({Task Code Number}, MATCH(Description@row, {Task Code Title}, 0)), "NA")
Thank you very much in advance.
Answers
-
jmyzk_cloudsmart_jp ✭✭✭✭✭Alternative to Dynamic dropdown is a subject I am very interested in.
I may have answered your question incorrectly this time, but I have come up with some solutions based on your shared data.
Just Get Code From Description by Index Match
Get Code from a table referencing CODE by INDEX and MATCH based on Description.
=IFERROR(INDEX({Code}, MATCH(Description@row, {Description}, 0), 1), "N/A")
Get the Code if part of the description match list.
Get CODE if part of the Description matches the Description of the referenced table.
=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell)), 1), "N/A")
Get Code if more than % description matches the list
Part of the above is to get the CODE if the match is more than the % specified in the Field of the Sheet Summary
=IFERROR(INDEX(COLLECT({Code}, {Description}, CONTAINS(Description@row, @cell), {Description}, LEN(Description@row) / LEN(@cell) > [%]#), 1 ), "N/A")
Next, here is an improved version of my recommendedmethod of displaying candidate dropdown lists from a separate sheet
Candidate based on the part of Description
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
=JOIN(COLLECT({Menu}, {Description}, CONTAINS(Description@row, @cell)), CHAR(10))
Candidate based on the part of Description: Ignore Case
Same as above, but case-insensitive.
=JOIN(COLLECT({Menu}, {Description}, CONTAINS(LOWER(Description@row), LOWER(@cell)))), CHAR(10))
Please take a look at a published dashboard demo.
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 witha way to display a listing if, for example, I put in part of the name of a person or product.
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.
It is a kind of dynamic dropdown list, so to speak. I think this is a practical solution.
In addition to this, I also present an alternative to the Dynamic Dropdown that automatically creates a table of lists from a master sheet.
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.
-
Hello@jmyzk_cloudsmart_jp, thank you very much for your reply. I can see how this is beneficial, especially with a long list. I will definitely keep these formulas for future use cases as I can see uses for other projects. However, for this case, I want to be able to show a warning or a flag if the task code is not a part of the job specified. I image this being in the form of "IF the cell contains a task code that is not on the Foreman Budget sheet, flag it."
Thank you again!
-
jmyzk_cloudsmart_jp ✭✭✭✭✭
What do you mean when you describe "if thattask codeis associated with thejob number"?
Do you mean ifJob#containsPhase Codein "23-0080 Forman Budget"?
Your Job# example,"23-0080," is the same as part of the sheet name, "23-0080Forman Budget", so I don't see what you are comparing task code with.
-
Yes, if Job# contains one of the task codes listed in the Foreman Budget sheet, then I want it to display as normal. If the task code is populated and is not part of the task code list for that job as shown in the Foreman budget sheet, then it needs to be flagged.
We have many different task codes and many different jobs, and since I am unable to have a dynamic drop down list based on the selected job number, I would like a task code that is not associated with that specific job number to be noticeable. Each job number has a foreman budget sheet that has all task codes for that job.
Is my request possible with the many different variations and sheets involved?
Does this make sense?
Thank you!
-
jmyzk_cloudsmart_jp ✭✭✭✭✭
I hope I have understood your intention properly.
=IFERROR(INDEX(COLLECT({Code}, {Descritption}, Description@row, {Code}, CONTAINS(@cell, [Job #]@row)), 1), "N/A")
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/comment/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/DJEO062T2TXG\/dropdown-list-alternative-demo-image.png<\/a><\/p>To solve this problem, we have devised a workaround that displays a list of two-digit numeric IDs and dropdown list items in a cell<\/strong> so that users can enter the two-digit numeric IDs corresponding to a list item.<\/p>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.<\/p>To try this solution, please refer to the sample dashboard in the following link.<\/p>https:\/\/app.smartsheet.com\/b\/publish?EQBCT=2580af6de6e845758f74b6556396d9ee<\/a><\/p>For more information, please refer to our homepage article and Youtube video in the following links.<\/p>https:\/\/cloudsmart.jp\/smartsheet\/templates\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\/<\/a><\/p>https:\/\/youtu.be\/IbZdlPTzN-k<\/a><\/p>","bodyRaw":"[{\"insert\":\"There have been many requests for improvements to the Dropdown list from Smartsheet Community.\\nThe two most typical requests are\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Automatic updating of the list \"},{\"attributes\":{\"bold\":true},\"insert\":{\"mention\":{\"name\":\"Justin Mauzy\",\"userID\":128829}}},{\"attributes\":{\"bold\":true},\"insert\":\" \"},{\"attributes\":{\"bold\":true},\"insert\":{\"mention\":{\"name\":\"Taylor Compton - NOAA Federal\",\"userID\":127663}}},{\"attributes\":{\"bold\":true},\"insert\":\" \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Dynamically changing the list of the following selections based on previous selections \"},{\"insert\":{\"mention\":{\"name\":\"Ian Cooper\",\"userID\":129416}}},{\"insert\":\" \"},{\"insert\":{\"mention\":{\"name\":\"Mehran Bagherian\",\"userID\":104586}}},{\"insert\":\" \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Regarding the first \"},{\"attributes\":{\"bold\":true},\"insert\":\"automatic updating of the list,\"},{\"insert\":\" 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.\\nAs for the second, \"},{\"attributes\":{\"bold\":true},\"insert\":\"dynamically changing the list of the following selections based on previous selections, \"},{\"insert\":\" some mimic dynamic or dependent dropdown lists, using \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481701-use-conditional-logic-to-streamline-form-submissions\"},\"insert\":\"form logic\"},{\"insert\":\" to 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.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DJEO062T2TXG\\\/dropdown-list-alternative-demo-image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DJEO062T2TXG\\\/dropdown-list-alternative-demo-image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"To solve this problem, we have devised a workaround that displays \"},{\"attributes\":{\"bold\":true},\"insert\":\"a list of two-digit numeric IDs and dropdown list items in a cell\"},{\"insert\":\" so that users can enter the two-digit numeric IDs corresponding to a list item.\\nAlthough 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.\\nTo try this solution, please refer to the sample dashboard in the following link.\\n\"},{\"insert\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=2580af6de6e845758f74b6556396d9ee\",\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=2580af6de6e845758f74b6556396d9ee\"}},{\"insert\":\"\\n\"},{\"insert\":\"For more information, please refer to our homepage article and Youtube video in the following links.\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/cloudsmart.jp\\\/smartsheet\\\/templates\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\\\/\"},\"insert\":\"https:\\\/\\\/cloudsmart.jp\\\/smartsheet\\\/templates\\\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features\\\/\"},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/youtu.be\\\/IbZdlPTzN-k\",\"attributes\":{\"link\":\"https:\\\/\\\/youtu.be\\\/IbZdlPTzN-k\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-05-26T03:56:27+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-08-13T09:00:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/105722\/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features","embedType":"quote","name":"Dropdown List Alternative with Automatic List Update and Dynamic List Features"}"> https://community.smartsheet.com/discussion/105722/dropdown-list-alternative-with-automatic-list-update-and-dynamic-list-features](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/DJEO062T2TXG\/dropdown-list-alternative-demo-image.png\"){kind=link}