DROP DOWN LISTS

[eternal_cat via OfficeKB.com]

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.

 

[Guest]

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.

 

[eternal_cat via OfficeKB.com]

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.

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.

 

[Guest]

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
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.

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.

 

[Gord Dibben]

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
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.

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.

 

[eternal_cat via OfficeKB.com]

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.

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]

 

[Gord Dibben]

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
have an autocomplete function in my suburbs column?

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.