Excel data validation multiple lists

[Guest]

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.

 

[Leo Heuser]

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.

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).

 

[Darren Hill]

First, create the lists you want to use somewhere on the spreadsheet, and
name each one - so the list you name "A" would contain 4 cells, with the
values "100,200,400,500" and so on.
Assume the cell containing the first list (A, B, etc) is cell A1.
Then, in the cell that is to contain this second list, in the data
validation formula enter "=Indirect(A1)"

That should do the trick.
--
Darren

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).

 

[Guest]

Thanks for the reply, that is what I was looking for

----- Darren Hill wrote: ----

First, create the lists you want to use somewhere on the spreadsheet, an
name each one - so the list you name "A" would contain 4 cells, with th
values "100,200,400,500" and so on
Assume the cell containing the first list (A, B, etc) is cell A1
Then, in the cell that is to contain this second list, in the dat
validation formula enter "=Indirect(A1)

That should do the trick
--
Darre

Hello, I am using Excel 97 for windows and working with data validation settings,allow:list

example, the cell will have a pull down list that gives the choices "A"
"B", "C", "D", etcdown list via validation should appear that is unique to the choice selecte
above. So, if "A" was previously selected, then in this cell, a validatio
list would appear with various choices based upon choosing "A" previously
For example, if "A" was selected previously, then in this cell, a validatio
list would appear that has the values "100,200,400,500" to choose from. I
"B" was selected previously, then this cell would have the value
"100,200,750,900,1200,1500,etc." to chose from. I have my data arranged i
various columns, but I do not know how to get the second cell to choose th
proper column based upon the first cell's selectionbased upon a previous cell's entry ? I have tried to enter a formula unde
"data validation/settings/allow:list/source" but it won't accept a formul
(if/then statement)

 

[Guest]

Thanks for the reply, it worked
----- Leo Heuser wrote: ----

Bria

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:L1
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 > Datavalidatio
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 th
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 Regard
Leo Heuse

Followup to newsgroup only, please

Hello, I am using Excel 97 for windows and working with data validation settings,allow:list

example, the cell will have a pull down list that gives the choices "A"
"B", "C", "D", etcdown list via validation should appear that is unique to the choice selecte
above. So, if "A" was previously selected, then in this cell, a validatio
list would appear with various choices based upon choosing "A" previously
For example, if "A" was selected previously, then in this cell, a validatio
list would appear that has the values "100,200,400,500" to choose from. I
"B" was selected previously, then this cell would have the value
"100,200,750,900,1200,1500,etc." to chose from. I have my data arranged i
various columns, but I do not know how to get the second cell to choose th
proper column based upon the first cell's selectionbased upon a previous cell's entry ? I have tried to enter a formula unde
"data validation/settings/allow:list/source" but it won't accept a formul
(if/then statement)

 

[Leo Heuser]

You're welcome.

LeoH