Prev: Rank order with conditions
Next: IF formula help
From: Hark2k on 30 Apr 2010 07:44
I have an excel file with a long list of names. We have it set up to do a
pick from list but we'd like to allow the user to type a letter and the list
will automatically skip to the names that start with that letter. For
instance, if they open like list and type "K" it will go to the list of names
that begin with K.

We are also interested in figuring out how to set up an Auto complete
function so that if the user starts typing in the persons name in the cell,
the list will narrow to only names that match the string of letters. In other
words, if the user starts to type "Jo" it will pull up Jones, Johnson and
Jonas. or if they continue to type and get to "Joh" it will leave only
Johnson.

Can anyone help me? Thanks!!!
From: Jacob Skaria on 30 Apr 2010 08:00
One option is to insert a combobox from menu View>Toolbars>ControlBox

--In design mode from Properties window set the property ListFillRange as
the range in which you have the names for example Sheet2!A1:A100

--and set you can link the control to a cell. Check out the property "Linked
Cell"



--
Jacob (MVP - Excel)


"Hark2k" wrote:

> I have an excel file with a long list of names. We have it set up to do a
> pick from list but we'd like to allow the user to type a letter and the list
> will automatically skip to the names that start with that letter. For
> instance, if they open like list and type "K" it will go to the list of names
> that begin with K.
>
> We are also interested in figuring out how to set up an Auto complete
> function so that if the user starts typing in the persons name in the cell,
> the list will narrow to only names that match the string of letters. In other
> words, if the user starts to type "Jo" it will pull up Jones, Johnson and
> Jonas. or if they continue to type and get to "Joh" it will leave only
> Johnson.
>
> Can anyone help me? Thanks!!!
From: Steve Dunn on 30 Apr 2010 08:29
Another option, if the list of names is of fixed length:
Insert enough rows above the data-entry cell for the list and in each cell
above the entry cell refer to the list, e.g.

=Sheet2!A1
=Sheet2!A2
=Sheet2!A3
....

You can hide these rows if necessary.

Then Excel's built-in auto-complete takes over in the entry cell. Start
typing and it will auto-complete from the list above, press alt+down to pick
from the list.

If you only have one entry cell, this is probably going to increase your
file size more than using a combo-box. However, if you have a contiguous
vertical range of entry cells, you may find that it produces a smaller file
than using multiple combo-boxes would.




"Hark2k" <Hark2k(a)discussions.microsoft.com> wrote in message
news:4C124F33-1ACC-49E4-AF9A-522F788CDE3E(a)microsoft.com...
>I have an excel file with a long list of names. We have it set up to do a
> pick from list but we'd like to allow the user to type a letter and the
> list
> will automatically skip to the names that start with that letter. For
> instance, if they open like list and type "K" it will go to the list of
> names
> that begin with K.
>
> We are also interested in figuring out how to set up an Auto complete
> function so that if the user starts typing in the persons name in the
> cell,
> the list will narrow to only names that match the string of letters. In
> other
> words, if the user starts to type "Jo" it will pull up Jones, Johnson and
> Jonas. or if they continue to type and get to "Joh" it will leave only
> Johnson.
>
> Can anyone help me? Thanks!!!

 | 
Pages: 1
Prev: Rank order with conditions
Next: IF formula help