General discussion

Locked

Question on Drop-down List in Excel

By Hunterzh ·
In Excel 2000, User can create a drop-down list from a range of cells, for example, "Europe", "Asia", for Column "Continent", so that operator can only select from the predefined list. Now we want to create two columns, for example, one column "Continent", another column "Country", hope to realize the function as: once operator select "Continent", let?s say, Europe, the Country column list only show UK, Germany, France, etc.; when he select Asia, the Country list only show China, Japan, India, etc.
After I select the first column, it will update the source for the second column in the same row.
How can I realize this? Thanks.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Bobsta In reply to Question on Drop-down Lis ...

Are all SP's installed for Office 2000?

Collapse -

by 3xp3rt In reply to Question on Drop-down Lis ...

So for example a1=Europe, a2=Asia
The range of countries is b1-b5 for Europe and b6-b9 for Asia
In cell d1 you can select the continent, and in cell e1 you can select the country.
When you validate the list of countries for the selected range write the following command IF(D1=A1, $B$1:$B$5,$B$6:$B$9) .
I try it and work. If you have most continents you can use the IF command like this
IF(D1=A1,$B$1:$B$5,IF(D1=A2,$B$6:$B$9,IF(D1=A3, $B$10:$B$13,$B$14:$B$20)))

Collapse -

Answer on Drop Down Lists in Excel

by simocross In reply to Question on Drop-down Lis ...

If your spreadsheet doesn't already have them, create a separate list of each type of product. Highlight each list in turn and choose Insert | Name | Define from the menu, using the category name as the name for this range. For our simple example, we'll put the three categories in the first three columns of a worksheet, with the category names at top. Then select the row of category names and use Insert | Name | Define to name that range Categories.


Let's suppose the user will choose a category in cell E2 and a product in cell F2. Click in E2 and select Validation from the Data menu. Select List from the drop-down titled Allow and enter =Categories in the Source box, then click OK. This establishes a simple list-based validation rule the user can enter only values found in the Categories range, and those values appear in a drop-down list.

Now click in cell F2, select Data | Validation, and choose List from the Allow drop-down, as before. This time in the Source box enter =INDIRECT(E2). This selects the range named by the contents of cell E2. If E2 contains "Cabinets," the list of cabinets will be presented in F2, and so on. When the user selects a different category in E2, the list in F2 changes. It is still possible to get a mismatch if the user chooses a product and then changes the category

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Forums