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) if that 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
-
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 recommended method 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 with a 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!
-
What do you mean when you describe "if that task code is associated with the job number"?🙄
Do you mean if Job# contains Phase Code in "23-0080 Forman Budget"?
Your Job# example, "23-0080," is the same as part of the sheet name, "23-0080 Forman 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!
-
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
Check out the Formula Handbook template!
