drop down list - a question from novice
 

I have a list of names of cities (Moscow, Ottawa, Toronto, and
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 cities
(alphabetical) but when I select a city I want to get the phone code in
the cell (numerical).
I am a novice in Excel, but I need to figure it out quite urgently, so
if someone can help I'd really appreciate that.
I am currently reading the book entitled "The Bible of Excel 2003" but
can't find an answer (although I'm sure it's there) because I can't
properly make a search query as I'm not a native English-speaking
person.


--
striker_69
------------------------------------------------------------------------
striker_69's Profile: http://www.excelforum.com/member.php...o&userid=27353
View this thread: http://www.excelforum.com/showthread...hreadid=468597

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

striker_69 wrote:

I have a list of names of cities (Moscow, Ottawa, Toronto, and
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 cities
(alphabetical) but when I select a city I want to get the phone code in
the cell (numerical).
I am a novice in Excel, but I need to figure it out quite urgently, so
if someone can help I'd really appreciate that.
I am currently reading the book entitled "The Bible of Excel 2003" but
can't find an answer (although I'm sure it's there) because I can't
properly make a search query as I'm not a native English-speaking
person.

--
striker_69
------------------------------------------------------------------------
striker_69's Profile: http://www.excelforum.com/member.php...o&userid=27353
View this thread: http://www.excelforum.com/showthread...hreadid=468597

--

Dave Peterson

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.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=468597

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"

striker_69 wrote:
I have a list of names of cities (Moscow, Ottawa, Toronto, and
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 cities
(alphabetical) but when I select a city I want to get the phone code in
the cell (numerical).
I am a novice in Excel, but I need to figure it out quite urgently, so
if someone can help I'd really appreciate that.
I am currently reading the book entitled "The Bible of Excel 2003" but
can't find an answer (although I'm sure it's there) because I can't
properly make a search query as I'm not a native English-speaking
person.




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

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


--
striker_69
------------------------------------------------------------------------
striker_69's Profile: http://www.excelforum.com/member.php...o&userid=27353
View this thread: http://www.excelforum.com/showthread...hreadid=468597

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


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=468597