Context Search

TexOrange

Is there a way to search a drop down list using certain key words, ie context sensitive search?

I have a sheet (Sheet A) that has a unique list of Part Names and Part Numbers. In a second sheet (Sheet B) i have two corresponding columns for Part Name and Part Number. Part Name has a drop down list for the user to pick a part name and the part number is auto populated. This works with no issues.

However, we have a lot of parts and I would like the user to have the ability to enter key words which would then filter and present a list of Part Names for the user to select. Today, the user needs the EXACT name to find the part name.

As an example:

Sheet A has the following Part Numbers and Part Names

111111 Engine Cooling Fan

222222 Auxiliary Fan Assembly

333333 Dynamic Friction Brake Pads

If the user types "fan" in Sheet B's (Part Name) drop down list, i would like the first two parts to be displayed.

Thanks in advance!

Paul Newcome

Unfortunately they are not going be able to make a specific selection directly from the list. What you will have to do is use two extra columns. One for the search value and another for the list. One the list populates they can manually enter the part into the part column.

Paul Newcome

I would suggest using this formula in a different cell:

=JOIN(COLLECT({Sheet A Part Name Column}, {Sheet A Part Name Column}, CONTAINS([Part Name]@row), CHAR(10))

And then enable text wrapping on the cell.

TexOrange

Paul - Thank you for your response. I tried your formula and unfortunately it did not work. I had to make a few changes. I get no errors and the cell is blank. I read somewhere that I may need the premium Data Shuttle app to make this work? Unsure if you have this feature installed.

If my information is incorrect and you got this to work without this app i would be interested to know. Thanks again. Much obliged for taking the time to provide a response.

Paul Newcome

I am using the base Smartsheet platform for this. Are you able to provide some screenshots for reference?

TexOrange

Absolutely. I ginned up a few images below.

SheetA - This is the list of parts

SheetB - Is the sheet a user would type in a partial part name in column "Part Name." As you can see the formula computes but is blank. I did modify the syntax for CONTAINS. When i used the syntax you sent, i get an error. Maybe I am making an error?

ReferenceRange - Wanted to show the range I selected for the COLLECT function.

What I would like is when a user types "Battery" in the column Part Name in SheetB, they should see three entries in column xCol to pick. Right now it is blank. Hope this makes sense.

I have shown the first row populated with Part Number in SheetB when the EXACT Part Name is selected (from a drop down). This uses an INDEX/MATCH function. So purposes of this exercise, pls ignore.

If the formula is hard to see in the image, below is what i used......

=JOIN(COLLECT({SheetA Range 1}, {SheetA Range 1}, CONTAINS([Part Name]@row, {SheetA Range 1})), CHAR(10))

Thanks again for taking the time.

Paul Newcome

My apologies. I did forget to finish out the CONTAINS function. Sometimes my brain and my fingers aren't moving at the same speed.

Try this:

=JOIN(COLLECT({SheetA Range 1}, {SheetA Range 1}, CONTAINS([Part Name]@row, @cell)), CHAR(10))

TexOrange

The formula works quite well to filter the values. Thanks again for your assistance.

I still have one issue though. I want the user to be able to select one of the filtered values such that the part number and Part Name are filled. So I tried to convert the Column xCol to a single select drop down. And it gives me the full list.

I am reading thru the community posts and thus far have not found a good solution.

Paul, thanks for taking the time. I am certain i can make use of this technique.

Paul Newcome

Unfortunately they are not going be able to make a specific selection directly from the list. What you will have to do is use two extra columns. One for the search value and another for the list. One the list populates they can manually enter the part into the part column.