Search Suggestion

[BarryP]

I'm building a spread sheet in Excel 2010 for a doctor. What I would like to do is have him type in the Medical term (or a string of matching text) in a cell and return a list of the defintions where that term or string of text is found. I have some 2300 definitions stored on a separate worksheet to pull from. Once he selects the definition from the list I want to have it populate the cell he began typing in if possible or another cell if not. I will then do a VLOOKUP in an adjacent cell with the corresponding Code number (this I can do).

Any help would be greatly appreciated

 

[mikerickson]

I'm not sure what you are looking for.

If the user types "head", do you want them to then select from
"headache"
"head nurse"
"head, broken"

or from

"top knob of body"
"person in charge"

i.e. is their final choice from a list of matching terms (i.e. contains "head") or defnintions (usually does not contain "head")

 

[BarryP]

I was hoping it would look like this this:
We type-in 'cyst'
and pull up a list of definitions with 'cyst' in it

Thyroid cyst
Cystic fibrosis
Cholecystitis, acute
Pancreatic cyst, pseudocyst

thanks for the response I haven't had much luck on my own

 

[mikerickson]

One way to do this would be to make a table with search terms ("cyst") down the left column and to the right of each search term, the list of definitions ("Thyroid cyst","Cystic firbrosis", "Cholecystitis, acute", "Pancreatic cyst, pseudocyst") in the cells to the right of the cell holding the search term.

Note that 1) the number of columns per row varies. 2)a definition may be in more than one row.

(A different table would be used for the definition/code numbers.)

Dependent named ranges could be used to populate a validation drop-down after the user enteres "cyst" in the search term cell.

Using cells requires that the user type "cyst" and enter it into the cell. The list of options would not change as the user is typing into the cell.

If you want the list to change as the user is typing, a userform and VBA would be needed.

 

[BarryP]

I have done that on another application and it worked well but in this case I'll probably have to do the VBA. I have zero experience in Visual Basics so I'll need some help with that. Thanks for the help.

 

[mikerickson]

I just want to be clear.
The association of "cyst" with its various defininitions has to be done, regardless of whether VBA or native Excel is used.

VBA is only needed if you want things to change as the user types. (That also requires a userform.)

If you are willing to wait for the user to press ENTER after typing in a search term, then cells and native Excel could be used.

 

[BarryP]

I see, I'll give it a try and let you know how I do.
Thanks again