I have a database of thousands of personnel and hundreds of column headings.
I want to make a 'search' function, where if I type in a name or part of a
name (followed by an asterisk), a drop down list will appear based upon the
search criteria (what I typed in). Then I want to be able to just select the
name of one individual so that I can populate a report with key info.
Currently I use index and match to bring up an individual, however whatever
name I type in followed by an asterisk gives me the first name that is
similar to what I am looking for. I need to see them all so that I can select
the right person.

Hi!

Close to what you want. (you're asking for a lot, though!
<g)

Is this acceptable?

You have to extract the names that meet the criteria to a
section on a worksheet then use that section as the source
for a dropdown list?

If that's ok:

Assume the names are in column H1:H5000.
Cell I1 is where you enter the search criteria. Don't use
the "*" as a wildcard. See explanation below.
Extract the list starting in cell AA1. Now, if you have
1,000's of names and it's possible that you may have 100's
of matches then you have to copy this formula to enough
rows that satisfy the number of matches.

Array entered with the key combo of CTRL,SHIFT,ENTER:

=IF(ISERROR(INDEX($H$1:$H$5000,SMALL(IF(LEFT
($H$1:$H$5000,1)=$I$1,ROW($H$1:$H$5000)),ROW
(1:1)))),"",INDEX($H$1:$H$5000,SMALL(IF(LEFT($H$1: $H$5000)
=$I$1,ROW($H$1:$H$5000)),ROW(1:1))))

When all matches have been found the formula returns "".

Now, I used LEFT() as the "match" criteria. I also have it
set for 1 character but you can change that to whatever
you like. So if you set it to 3 and enter "tim" in cell I1
the formula will return all names that start with those 3
letters.

Now, create a dynamic named range from the list of
extracted names: Csll it "list", or whatever.

=OFFSET($AA$1,0,0,SUMPRODUCT(--($AA$1:$aa$65000<"")),1)


Now, create a data validation dropdown list and use the
dynamic named range of LIST as the source.

This is as close as you're going to get to what you wanted
unless one of the super guru's checks in!

Biff

-----Original Message-----
I have a database of thousands of personnel and hundreds
of column headings.
I want to make a 'search' function, where if I type in a
name or part of a
name (followed by an asterisk), a drop down list will
appear based upon the
search criteria (what I typed in). Then I want to be able
to just select the
name of one individual so that I can populate a report
with key info.
Currently I use index and match to bring up an
individual, however whatever
name I type in followed by an asterisk gives me the first
name that is
similar to what I am looking for. I need to see them all
so that I can select
the right person.
.

You could us an advanced filter to create a short list of names and base
the data validation dropdown list on that. Index and match formulas can
return the matching data.

There's an example he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Limit Selection List'

BigPig wrote:
I have a database of thousands of personnel and hundreds of column headings.
I want to make a 'search' function, where if I type in a name or part of a
name (followed by an asterisk), a drop down list will appear based upon the
search criteria (what I typed in). Then I want to be able to just select the
name of one individual so that I can populate a report with key info.
Currently I use index and match to bring up an individual, however whatever
name I type in followed by an asterisk gives me the first name that is
similar to what I am looking for. I need to see them all so that I can select
the right person.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra,

I tried using what you had as an example, however it did not work. I named
the database, extract, etc, even copied the event code and pasted into my
worksheet, then changed the sheet names. When I type in a letter or more, and
then select the drop down, I get the whole list of names. If you could offer
any pointers, I would be grateful.

"Debra Dalgleish" wrote:

You could us an advanced filter to create a short list of names and base
the data validation dropdown list on that. Index and match formulas can
return the matching data.

There's an example he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Limit Selection List'

Did you use define a dynamic range named "NameList" ?

To see the definition in the sample workbook, choose InsertName.

BigPig wrote:
Hi Debra,

I tried using what you had as an example, however it did not work. I named
the database, extract, etc, even copied the event code and pasted into my
worksheet, then changed the sheet names. When I type in a letter or more, and
then select the drop down, I get the whole list of names. If you could offer
any pointers, I would be grateful.

"Debra Dalgleish" wrote:


You could us an advanced filter to create a short list of names and base
the data validation dropdown list on that. Index and match formulas can
return the matching data.

There's an example he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Limit Selection List'




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html