Conditional Dropdown Menus
Posted by Rick Behrens on November 20, 2001 11:30 AM
I would like to use the validation feature to create a "picklist" or dropdown menu for a cell. That par is easy, but the catch is that I want the choices in the cell to be determined based on the criteria from another cell by searching another worksheet to find all of the choices that meet that criteria.
For instance, let sheet 1 contain a column of states (A) and a column of cities (B) where there are several cities for each state. On sheet 2, let cell A:1 be used to enter a state. I would like cell B:1 to be a picklist of all of the cities in that state.
Thanks for the help.
Posted by Aladin Akyurek on November 20, 2001 12:14 PM
Select all of the cells of the list containing States (excluding any label you might have), go the Name Box on the Formula Bar, type STATES, and enter.
Select all of the cells that contain cities of a particular state (say, CA), activate the Name Box, and type CA. CA must be one of the items in your list we named STATES. Do this for every state that is in the STATES list.
On Sheet2:
Activate A1.
Activate Data|Validation.
Choose List for Allow.
Enter as Source:
=STATES
Click OK.
Activate B1.
Activate Data|Validation.
Choose List for Allow.
Enter as Source:
=INDIRECT(A1)
Click OK.
Aladin
===========
Posted by Richard S on November 20, 2001 4:49 PM
Big Bob Order From Blues - This will help (nt)
Posted by Rick Behrens on November 20, 2001 11:01 PM
Okay, I think I might have over simplified my problem. Let me make it a bit more complicated. Now... On sheet 1, make column A Cities and column B addresses.
I have hundreds of cites with several addresses each, but here is the catch: the cities and addresses change every day (they are pulled from a database). To make things even more complicated, they might not be grouped together in neat arrays.
I'm sorry to switch gears on you, but I'm trying to simplify my problem into terms that are easily understood. I hope there is still a good solution. : Select all of the cells of the list containing States (excluding any label you might have), go the Name Box on the Formula Bar, type STATES, and enter. : Select all of the cells that contain cities of a particular state (say, CA), activate the Name Box, and type CA. CA must be one of the items in your list we named STATES. Do this for every state that is in the STATES list. : On Sheet2
Posted by Ian Mac on November 23, 2001 4:40 AM
If they are pulled form a database, why not run the queries you want in the database?
I have hundreds of cites with several addresses each, but here is the catch: the cities and addresses change every day (they are pulled from a database). To make things even more complicated, they might not be grouped together in neat arrays. I'm sorry to switch gears on you, but I'm trying to simplify my problem into terms that are easily understood. I hope there is still a good solution.
Posted by Rick on November 24, 2001 3:41 PM
Re: If they are pulled form a database, why not run the queries you want in the database?
The database is not actually accessable. So I run a query and save the result as a text file. I have not really been able to figure out how to use these text files as a database. If there is an easy way to do that let me know. : Okay, I think I might have over simplified my problem. Let me make it a bit more complicated. Now... On sheet 1, make column A Cities and column B addresses. : I have hundreds of cites with several addresses each, but here is the catch: the cities and addresses change every day (they are pulled from a database). To make things even more complicated, they might not be grouped together in neat arrays. : I'm sorry to switch gears on you, but I'm trying to simplify my problem into terms that are easily understood. I hope there is still a good solution.
