DROP DOWN LISTS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

eternal_cat - 23 Nov 2005 23:34 GMT

Hi, i was wanting to know how to make a drop down list for column F where the
contents of this list would be all the suburbs i had entered into that column
previously. Similar to the pick from list option.

Thanks.

cat.

Reply to this Message

Ken - 23 Nov 2005 23:46 GMT

Easy.

Select the cell where you want to place the Drop Down.
From the Menu bar, select Data, then Validation.
On the Settings tab of the popup select List from the Drop Down.
Then click on the red arrow icon on the right side of the Source Box.
Now select the range of cells that contains your list.
Press Enter and click OK.

> Hi, i was wanting to know how to make a drop down list for column F where the
> contents of this list would be all the suburbs i had entered into that column
[quoted text clipped - 3 lines]
>
> cat.

Reply to this Message

eternal_cat - 24 Nov 2005 00:39 GMT

K, I wanted it to contain only one instance of each suburb, whereas the list
is containing all the instances of the suburbs in the range that i have
selected. I also want to have this list so that it will autofill the suburbs
as i type them (since there is only one instance of each suburb). Like in
Excel help in the index when you type in a word and it automatically scrolls
down to a matching word.

thanks.

>Easy.
>
[quoted text clipped - 10 lines]
>>
>> cat.

Reply to this Message

Ken - 24 Nov 2005 01:08 GMT

You could make a separate list of just the cells you want. You could then
hide that list or place it off the page so it isn't seen.

As for making the list autofill, I don't think you can do that in a Drop
Down List.

> K, I wanted it to contain only one instance of each suburb, whereas the list
> is containing all the instances of the suburbs in the range that i have
[quoted text clipped - 19 lines]
> >>
> >> cat.

Reply to this Message

Gord Dibben - 24 Nov 2005 01:36 GMT

First you have to delete the duplicates then make your list from the remaining
suburbs.

Debra Dalgleish gives instructions for retrieving uniques from a list of
duplicates.

http://www.contextures.on.ca/xladvfilter01.html#FilterUR

DV lists do not enable autocomplete.

You need a combo box for this.

See Debra's site for this also.

http://www.contextures.on.ca/xlDataVal10.html

Gord Dibben Excel MVP

>K, I wanted it to contain only one instance of each suburb, whereas the list
>is containing all the instances of the suburbs in the range that i have
[quoted text clipped - 19 lines]
>>>
>>> cat.

Reply to this Message

eternal_cat - 28 Nov 2005 02:20 GMT

right, so i have to create a lookup sheet? I cant really delete the
duplicates, cos they are part of a valid record. Will all this help me to
have an autocomplete function in my suburbs column?

>First you have to delete the duplicates then make your list from the remaining
>suburbs.
[quoted text clipped - 19 lines]
>>>>
>>>> cat.

Reply to this Message

Gord Dibben - 28 Nov 2005 04:10 GMT

You want to filter just the unique suburbs and paste that list to another
worksheet.

Your original data can remain untouched, duplicates and all.

Name the range of that list from the second sheet and use it for the drop-down
list which you place into the combo-box.

The combo-box allows auto-complete.

Debra's sites shows how to do both the above.

Gord Dibben Excel MVP

>right, so i have to create a lookup sheet? I cant really delete the
>duplicates, cos they are part of a valid record. Will all this help me to
[quoted text clipped - 23 lines]
>>>>>
>>>>> cat.

Reply to this Message