drop down list - a question from novice

S

striker_69

I have a list of names of cities (Moscow, Ottawa, Toronto, an
Washington DC)
and
a list of corresponding phone codes:
Moscow: +7 (095)
Ottawa: +1 (613)
Toronto: +1 (416)
Washington DC: +1 (202)

I need to make a drop down list in a cell which would work as follows
when I click on it, I will see the dropdown list of citie
(alphabetical) but when I select a city I want to get the phone code i
the cell (numerical).
I am a novice in Excel, but I need to figure it out quite urgently, s
if someone can help I'd really appreciate that.
I am currently reading the book entitled "The Bible of Excel 2003" bu
can't find an answer (although I'm sure it's there) because I can'
properly make a search query as I'm not a native English-speakin
person
 
D

Dave Peterson

Build a table on another worksheet.
Put all the cities in column A
and put the codes in column B

And sort both those columns by column A (and sort it each time you update that
list)

Then use data|validation to get the dropdown options on your other sheet.

Take a look at Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html
for more info about data|validation.

Then use an adjacent cell and put this formula (I used A1 to hold my
data|validation cell):

=if(a1="","",vlookup(a1,sheet2!a:b,2,false))

For more info about =vlookup()...
You could look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
 
C

Cutter

You need to construct a table placing your city names in one column and
your phone codes in another

In the cell where you want your drop down list to be use Data
Validation (Go to the menu bar and select Data > Validation) and limit
the cell input to your list of cities in your table

In the cell where you want your phone code to be place a VLOOKUP()
formula that refers to your cell containing your drop down and your
table containing the cities and phone codes.

Since you have an excellent resource to help you I won't give you the
step by step instructions. You learn better by doing it yourself.

So seek help on Data Validation Lists and VLOOKUP() formula.

If you get stuck then just post back to this thread.
 
D

Debra Dalgleish

You could do this with programming. There's a sample workbook on my web
site that shows a product name and code in the Data Validation dropdown
list. After an item is selected, the cell shows only the product name.
You may be able to adapt this to your workbook.

On the following page:

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

Under the data validation heading, look for 'Data Validation "Columns"
 
S

striker_69

thanks a lot to everybody - I will give it a try. The thing is I'v
been able to do simple operations in Excel but it seems to be such a
interesting program that I am now really curious to figure it out, jus
like I did for Wor
 
C

comotoman

If you have anymore questions, i just built a drop down list in the same
format that you need.
 
Top