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.

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Jadon Meador

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.😀

Formula as alternative to Dynamic dropdown menus simple INDEX MATCH.png

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

Formula as alternative to Dynamic dropdown menus Show List based on Input.png

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.

Formula as alternative to Dynamic dropdown menus Demo DB.png

https://app.smartsheet.com/b/publish?EQBCT=bb7e2067fe42460da83062cb15777d74

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.

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

Jadon Meador

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

Hi @Jadon Meador

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.

Jadon Meador

@jmyzk_cloudsmart_jp

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

Hi @Jadon Meador

I hope I have understood your intention properly.

=IFERROR(INDEX(COLLECT({Code}, {Descritption}, Description@row, {Code}, CONTAINS(@cell, [Job #]@row)), 1), "N/A")

https://app.smartsheet.com/b/publish?EQBCT=b4485e560a404ddba951bd8fe1f2ee1b

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.