Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list with an if statement | Excel Worksheet Functions | |||
Drop Down List for Chart | Excel Discussion (Misc queries) | |||
select drop down list 1 value will change the value in dr... | Excel Worksheet Functions | |||
Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions |