Brian

Here's one way to do it:

A1 is the cell to hold the list A, B, C, .....
B1 will hold the chosen validation list.

Assuming 3 namelists, one in K3:K10, one in L3:L10
and the last one in M3:M10.

1. Select K3:M10 and name it "NameRange" (without quotes)
Use the namebox at the far left of the formula bar.
2. Select B1 and choose Data Datavalidation
3. Choose "List" in "Allow"
4. In "Source" enter this formula:
=OFFSET(NameRange,0,CODE($A$1)-65,COUNTA(
OFFSET(NameRange,0,CODE($A$1)-65,,1)),1)
5. OK.

If the lists vary in length and possible empty cells don't occur "in the
middle" of the lists, this setup will only display the non-empty cells.

If you enter A in A1, the list to choose from in B1 will be K3:K10,
if you enter B, the list will be L3:L10 and so on.


--
Best Regards
Leo Heuser

Followup to newsgroup only, please.


"Brian J Cassidy" skrev i en
meddelelse ...
Hello, I am using Excel 97 for windows and working with data validation,
settings,allow:list.

I am trying to do the following:

In one cell, the user selects from a list created via validation. For
example, the cell will have a pull down list that gives the choices "A",
"B", "C", "D", etc.

Then, in another cell, based upon which choice was selected above, a pull
down list via validation should appear that is unique to the choice selected
above. So, if "A" was previously selected, then in this cell, a validation
list would appear with various choices based upon choosing "A" previously.
For example, if "A" was selected previously, then in this cell, a validation
list would appear that has the values "100,200,400,500" to choose from. If
"B" was selected previously, then this cell would have the values
"100,200,750,900,1200,1500,etc." to chose from. I have my data arranged in
various columns, but I do not know how to get the second cell to choose the
proper column based upon the first cell's selection.

How do I create the entry for the second cell which will choose a list
based upon a previous cell's entry ? I have tried to enter a formula under
"data validation/settings/allow:list/source" but it won't accept a formula
(if/then statement).

Any help would be greatly appreciated. Thanks.