List values based on different values selected

[maniarasan]

State :
Type1 :
Type2 :
Type3 :

Alaska Arizona
Type1 Alaska1 Type1 Arizona1
Type2 Alaska2 Type2 Arizona2
Type3 Alaska3 Type3 Arizona3


Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
1.1 2.1 3.1 11.1 12.1 13.1
1.2 2.2 3.2 11.2 12.2 13.2
1.3 2.3 3.3 11.3 12.3 13.3
1.4 2.4 3.4 11.4 12.4
1.5 2.5 3.5 11.5 12.5

Above is the worksheet which i'm trying to solve.
When state "Alaska" is selected, Type 1 should list the values 1.1 -
1.5; Type 2 should list the values 2.1 - 2.5, so on
When State "Arizona" is selected, type 1 should list the values 11.1 -
11.5 and so on

Please help me on this.....

 

[steve]

What exactly do you mean by "selected" where does this selection take place,
and where should the list then show up.

Regards
Steve

 

[maniarasan]

What exactly do you mean by "selected" where does this selection take place,
and where should the list then show up.

Regards









- Show quoted text -

The selection will be made from the dropdown list "State", such that
the values should be listed in type1, type2, type3 as dropdown list
based on state selected.

 

[maniarasan]

The selection will be made from the dropdown list "State", such that
the values should be listed in type1, type2, type3 as dropdown list
based on state selected.- Hide quoted text -

- Show quoted text -

I tried with INDIRECT function but it doesn't worked out completely,
any suggestins would be appreciated

 

[steve]

I think I am starting to understand, as you have access to the news groups
could you try looking in "microsoft.public.excel.programming". I think that
the thread started by "Michael Hudston" about an hour ago, might answer your
question.

Regards
Steve

The selection will be made from the dropdown list "State", such that
the values should be listed in type1, type2, type3 as dropdown list
based on state selected.- Hide quoted text -

- Show quoted text -

I tried with INDIRECT function but it doesn't worked out completely,
any suggestins would be appreciated

 

[maniarasan]

I think I am starting to understand, as you have access to the news groups
could you try looking in "microsoft.public.excel.programming". I think that
the thread started by "Michael Hudston" about an hour ago, might answer your
question.

Regards





I tried with INDIRECT function but it doesn't worked out completely,
any suggestins would be appreciated- Hide quoted text -

- Show quoted text -

It doesn't works out as i need to list all the three types with the
single selection (i.e. state)

 

[Steve Dunn]

If "State:" is in A1 and your data is in A12:F17, put this in B2:

=INDEX($A$12:$F$17,COLUMN()-COLUMN($B$1)+2,
MATCH($B$1&ROW()-ROW($B$1),$A$12:$F$12,0))

Copy across and and down as far as F4

HTH
Steve D.

 

[steve]

Hi maniarasan,

The answer is easy, but the hard part is the question, could you be more
specific.
Is State: a named range and is that used as data validation in a cell which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like to
have the contents change depending on the selection of State:
Should Type!: etc also form a drop down list in a cell with data
validation.

Could you tell me which cells contain the drop down lists and how you have
(done/want to do) this.

Stick with it we will get there.

Regards
Steve

I think I am starting to understand, as you have access to the news groups
could you try looking in "microsoft.public.excel.programming". I think
that
the thread started by "Michael Hudston" about an hour ago, might answer
your
question.

Regards





I tried with INDIRECT function but it doesn't worked out completely,
any suggestins would be appreciated- Hide quoted text -

- Show quoted text -

It doesn't works out as i need to list all the three types with the
single selection (i.e. state)

 

[maniarasan]

If "State:" is in A1 and your data is in A12:F17, put this in B2:

=INDEX($A$12:$F$17,COLUMN()-COLUMN($B$1)+2,
MATCH($B$1&ROW()-ROW($B$1),$A$12:$F$12,0))

Copy across and and down as far as F4

HTH
Steve D.










- Show quoted text -

Hi Steve Dunn,

//If "State:" is in A1 and your data is in A12:F17, put this in B2:

=INDEX($A$12:$F$17,COLUMN()-COLUMN($B$1)+2,
MATCH($B$1&ROW()-ROW($B$1),$A$12:$F$12,0))

Copy across and and down as far as F4 //

This is matching the requirement to an extent, but I want the Type1,
Type2, Type3 values to be listed as dropdown in B2, B3and B4 based on
the selection of the state from dropdown B1.

thanks
Maniarasan

 

[maniarasan]

Hi maniarasan,

The answer is easy, but the hard part is the question, could you be more
specific.
Is State: a named range and is that used as data validation in a cell which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like to
have the contents change depending on the selection of State:
Should Type!: etc also form a drop down list  in a cell with data
validation.

Could you tell me which cells contain the drop down lists and how you have
(done/want to do) this.

Stick with it we will get there.

Regards
  Steve








It doesn't works out as i need to list all the three types with the
single selection (i.e. state)- Hide quoted text -

- Show quoted text -

Hi Steve,

//* Is State: a named range and is that used as data validation in a
cell which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like
to
have the contents change depending on the selection of State: *//

Yes, above statement is correct...

Consider states such as (Alaska, Arizona....) are listed in dropdown
cell B1, and the type1 values Alaska1 listed in A13 to A17 and
Arizona1 values listed D13 to D17, type2 values Alaska2 listed in B13
to B17 and Arizona2 values listed E13 to E17, type3 values Alaska3
listed in C13 to C17 and Arizona3 values listed F13 to F17.

Then if Alaska is selected in dropdown B1, then the values for Alaska1
should be listed as dropdown in cell B2; values for Alaska2 should be
listed as dropdown in cell B3; values for Alaska3 should be listed as
dropdown in cell B4.

if Arizona is selected in dropdown B1, then the values for Arizona1
should be listed as dropdown in cell B2; values for Arizona2 should be
listed as dropdown in cell B3; values for Arizona3 should be listed as
dropdown in cell B4.\

Below is the worksheet model:

State(A1) Alaska(B1 - Dropdown)
Type 1(A2) 1.1-1.5(B2 - list as dropdown)
Type 2(A3) 2.1-2.5(B3 - list as dropdown)
Type 3(A4) 3.1-3.5(B4 - list as dropdown)




A12 Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
A13 1.1 2.1 3.1 11.1 12.1 13.1
A14 1.2 2.2 3.2 11.2 12.2 13.2
A15 1.3 2.3 3.3 11.3 12.3 13.3
A16 1.4 2.4 3.4 11.4 12.4
A17 1.5 2.5 3.5 11.5 12.5

Thanks,
Maniarasan

 

[stunn]

Okay, so put the formula in a spare sheet, and use the results range
as your list values in the dropdowns.

 

[maniarasan]

Okay, so put the formula in a spare sheet, and use the results range
as your list values in the dropdowns.










- Show quoted text -

Is there any way to modify the same formula with data validations to
list the values as dropdown...

 

[steve]

Hi,
Sorry for the delay. I just managed to get back to the newsgroups after
microsoft dropped them. If all your named ranges are 'State' , 'Alaska1' ,
'Alaska2' , etc.then the settings for data validation in B2 should read;
"Allow" --- pick 'list'
"Source" -- =INDIRECT($B$1 & RIGHT(A2,1))
Once you have created the validation rule for cell B2 you can select B2 and
Copy/Fill down to B4 this will automatically create the validation rules in
B3 and B4.

Hope this helps

Regards
Steve

Hi maniarasan,

The answer is easy, but the hard part is the question, could you be more
specific.
Is State: a named range and is that used as data validation in a cell
which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like to
have the contents change depending on the selection of State:
Should Type!: etc also form a drop down list in a cell with data
validation.

Could you tell me which cells contain the drop down lists and how you have
(done/want to do) this.

Stick with it we will get there.

Regards
Steve








It doesn't works out as i need to list all the three types with the
single selection (i.e. state)- Hide quoted text -

- Show quoted text -

Hi Steve,

//* Is State: a named range and is that used as data validation in a
cell which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like
to
have the contents change depending on the selection of State: *//

Yes, above statement is correct...

Consider states such as (Alaska, Arizona....) are listed in dropdown
cell B1, and the type1 values Alaska1 listed in A13 to A17 and
Arizona1 values listed D13 to D17, type2 values Alaska2 listed in B13
to B17 and Arizona2 values listed E13 to E17, type3 values Alaska3
listed in C13 to C17 and Arizona3 values listed F13 to F17.

Then if Alaska is selected in dropdown B1, then the values for Alaska1
should be listed as dropdown in cell B2; values for Alaska2 should be
listed as dropdown in cell B3; values for Alaska3 should be listed as
dropdown in cell B4.

if Arizona is selected in dropdown B1, then the values for Arizona1
should be listed as dropdown in cell B2; values for Arizona2 should be
listed as dropdown in cell B3; values for Arizona3 should be listed as
dropdown in cell B4.\

Below is the worksheet model:

State(A1) Alaska(B1 - Dropdown)
Type 1(A2) 1.1-1.5(B2 - list as dropdown)
Type 2(A3) 2.1-2.5(B3 - list as dropdown)
Type 3(A4) 3.1-3.5(B4 - list as dropdown)




A12 Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
A13 1.1 2.1 3.1 11.1 12.1 13.1
A14 1.2 2.2 3.2 11.2 12.2 13.2
A15 1.3 2.3 3.3 11.3 12.3 13.3
A16 1.4 2.4 3.4 11.4 12.4
A17 1.5 2.5 3.5 11.5 12.5

Thanks,
Maniarasan

 

[maniarasan]

Hi,
  Sorry for the delay. I just managed to get back to the newsgroups after
microsoft dropped them. If all your named ranges are 'State' , 'Alaska1' ,
'Alaska2' , etc.then the settings for data validation in B2 should read;
"Allow"  ---   pick 'list'
"Source"  --   =INDIRECT($B$1 & RIGHT(A2,1))
Once you have created the validation rule for cell B2 you can select B2 and
Copy/Fill down to B4 this will automatically create the validation rules in
B3 and B4.

Hope this helps

Regards









Hi Steve,

//* Is State: a named range and is that used as data validation in a
cell which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like
to
have the contents change depending on the selection of State: *//

Yes, above statement is correct...

Consider states such as (Alaska, Arizona....) are listed in dropdown
cell B1, and the type1 values Alaska1 listed in A13 to A17 and
Arizona1 values listed D13 to D17, type2 values Alaska2 listed in B13
to B17 and Arizona2 values listed E13 to E17, type3 values Alaska3
listed in C13 to C17 and Arizona3 values listed F13 to F17.

Then if Alaska is selected in dropdown B1, then the values for Alaska1
should be listed as dropdown in cell B2; values for Alaska2 should be
listed as dropdown in cell B3; values for Alaska3 should be listed as
dropdown in cell B4.

if Arizona is selected in dropdown B1, then the values for Arizona1
should be listed as dropdown in cell B2; values for Arizona2 should be
listed as dropdown in cell B3; values for Arizona3 should be listed as
dropdown in cell B4.\

Below is the worksheet model:

State(A1)      Alaska(B1 - Dropdown)
Type 1(A2)     1.1-1.5(B2 - list as dropdown)
Type 2(A3)     2.1-2.5(B3 - list as dropdown)
Type 3(A4)     3.1-3.5(B4 - list as dropdown)

A12   Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
A13     1.1                    2.1 3.1 11.1 12.1 13.1
A14     1.2                    2.2 3.2 11.2 12.2 13.2
A15     1.3                    2.3 3.3 11.3 12.3 13.3
A16     1.4                    2.4 3.4 11.4 12.4
A17     1.5                    2.5 3.5 11.5 12.5

Thanks,
Maniarasan- Hide quoted text -

- Show quoted text -

Hi Steve,

/* "Source" -- =INDIRECT($B$1 & RIGHT(A2,1)) */

Great!!!! the above validation rule exactly meets my requirement,
thanks for putting your effort...

Regards
Maniarasan

 

[steve]

Hi,
You are wlecome

Regards
Steve

Hi,
Sorry for the delay. I just managed to get back to the newsgroups after
microsoft dropped them. If all your named ranges are 'State' , 'Alaska1' ,
'Alaska2' , etc.then the settings for data validation in B2 should read;
"Allow" --- pick 'list'
"Source" -- =INDIRECT($B$1 & RIGHT(A2,1))
Once you have created the validation rule for cell B2 you can select B2
and
Copy/Fill down to B4 this will automatically create the validation rules
in
B3 and B4.

Hope this helps

Regards









Hi Steve,

//* Is State: a named range and is that used as data validation in a
cell which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like
to
have the contents change depending on the selection of State: *//

Yes, above statement is correct...

Consider states such as (Alaska, Arizona....) are listed in dropdown
cell B1, and the type1 values Alaska1 listed in A13 to A17 and
Arizona1 values listed D13 to D17, type2 values Alaska2 listed in B13
to B17 and Arizona2 values listed E13 to E17, type3 values Alaska3
listed in C13 to C17 and Arizona3 values listed F13 to F17.

Then if Alaska is selected in dropdown B1, then the values for Alaska1
should be listed as dropdown in cell B2; values for Alaska2 should be
listed as dropdown in cell B3; values for Alaska3 should be listed as
dropdown in cell B4.

if Arizona is selected in dropdown B1, then the values for Arizona1
should be listed as dropdown in cell B2; values for Arizona2 should be
listed as dropdown in cell B3; values for Arizona3 should be listed as
dropdown in cell B4.\

Below is the worksheet model:

State(A1) Alaska(B1 - Dropdown)
Type 1(A2) 1.1-1.5(B2 - list as dropdown)
Type 2(A3) 2.1-2.5(B3 - list as dropdown)
Type 3(A4) 3.1-3.5(B4 - list as dropdown)

A12 Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
A13 1.1 2.1 3.1 11.1 12.1 13.1
A14 1.2 2.2 3.2 11.2 12.2 13.2
A15 1.3 2.3 3.3 11.3 12.3 13.3
A16 1.4 2.4 3.4 11.4 12.4
A17 1.5 2.5 3.5 11.5 12.5

Thanks,
Maniarasan- Hide quoted text -

- Show quoted text -

Hi Steve,

/* "Source" -- =INDIRECT($B$1 & RIGHT(A2,1)) */

Great!!!! the above validation rule exactly meets my requirement,
thanks for putting your effort...

Regards
Maniarasan

 

[stunn]

Is there any way to modify the same formula with data validations to
list the values as dropdown...- Hide quoted text -

- Show quoted text -

I hate to say no, because someone nearly always proves "no" to be
wrong, but, No. The only way would be to put this in cells on a
spreadsheet and refer the data validation list to them.

HTH
Steve D.