data validation from multiple lists
 

i have data validation for a cell set to allow data from a list using a named
range. can i make the name of the range in the data validation source field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c respectively
and want the drop down in E1 to display only 1 of the 3 lists depending on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

You posted to Programming, so you may want a VBA solution, however, this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using a
named
range. can i make the name of the range in the data validation source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c respectively
and want the drop down in E1 to display only 1 of the 3 lists depending on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will only
allow 29 values. I actually have 54 lists to choose from. Is there a way
around this? I tried breaking my lists into 2 groups but Excel doesn't allow
an IF function in the source field in data validation.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however, this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using a
named
range. can i make the name of the range in the data validation source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c respectively
and want the drop down in E1 to display only 1 of the 3 lists depending on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

--
Regards,
Tom Ogilvy

"dkingston" wrote in message
...
The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will
only
allow 29 values. I actually have 54 lists to choose from. Is there a way
around this? I tried breaking my lists into 2 groups but Excel doesn't
allow
an IF function in the source field in data validation.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however, this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example
workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using a
named
range. can i make the name of the range in the data validation source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c
respectively
and want the drop down in E1 to display only 1 of the 3 lists depending
on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

I have run into the same 29 limit problem. Could not find a way around it.

I see Tom has offered a solution, however, I am having trouble deciphering
it.

Regards,
Howard

"dkingston" wrote in message
...
The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will
only
allow 29 values. I actually have 54 lists to choose from. Is there a way
around this? I tried breaking my lists into 2 groups but Excel doesn't
allow
an IF function in the source field in data validation.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however, this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example
workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using a
named
range. can i make the name of the range in the data validation source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c
respectively
and want the drop down in E1 to display only 1 of the 3 lists depending
on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

Hi Tom,

Could you please elaborate a bit on your formula? I'm not getting the jest
of it.

Thanks.
Regards,
Howard

"Tom Ogilvy" wrote in message
...
=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

--
Regards,
Tom Ogilvy

"dkingston" wrote in message
...
The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will
only
allow 29 values. I actually have 54 lists to choose from. Is there a way
around this? I tried breaking my lists into 2 groups but Excel doesn't
allow
an IF function in the source field in data validation.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however, this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example
workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using a
named
range. can i make the name of the range in the data validation source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c
respectively
and want the drop down in E1 to display only 1 of the 3 lists
depending on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

=IF(AND(D1=1,D1<=6),IF(D1<=3,CHOOSE(D1,"A","B","C "),CHOOSE(D1-3,"D","E","F")),"")

in D1 successively enter the numbers 1 to 6 inclusive.

--
Regards,
Tom Ogilvy


"L. Howard Kittle" wrote in message
...
Hi Tom,

Could you please elaborate a bit on your formula? I'm not getting the
jest of it.

Thanks.
Regards,
Howard

"Tom Ogilvy" wrote in message
...
=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

--
Regards,
Tom Ogilvy

"dkingston" wrote in message
...
The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will
only
allow 29 values. I actually have 54 lists to choose from. Is there a way
around this? I tried breaking my lists into 2 groups but Excel doesn't
allow
an IF function in the source field in data validation.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however,
this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example
workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using
a
named
range. can i make the name of the range in the data validation source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c
respectively
and want the drop down in E1 to display only 1 of the 3 lists
depending on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

Thanks, Tom, I'll give it a go.

Regards,
Howard

"Tom Ogilvy" wrote in message
...
=IF(AND(D1=1,D1<=6),IF(D1<=3,CHOOSE(D1,"A","B","C "),CHOOSE(D1-3,"D","E","F")),"")

in D1 successively enter the numbers 1 to 6 inclusive.

--
Regards,
Tom Ogilvy


"L. Howard Kittle" wrote in message
...
Hi Tom,

Could you please elaborate a bit on your formula? I'm not getting the
jest of it.

Thanks.
Regards,
Howard

"Tom Ogilvy" wrote in message
...
=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

--
Regards,
Tom Ogilvy

"dkingston" wrote in message
...
The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will
only
allow 29 values. I actually have 54 lists to choose from. Is there a
way
around this? I tried breaking my lists into 2 groups but Excel doesn't
allow
an IF function in the source field in data validation.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however,
this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example
workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using
a
named
range. can i make the name of the range in the data validation
source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c
respectively
and want the drop down in E1 to display only 1 of the 3 lists
depending on
the value in cell D2 how do i set up the data validation field?

data validation from multiple lists
 

Hi Tom,

Okay, got it, and thanks. I expanded to 29 choices for each CHOOSE, a1 to
a29 and b1 to b29. Was having trouble with the second CHOOSE with the
formula as below. Would only return value if false.

IF(AND(D1=1,D1<=29),IF(D1<=29,CHOOSE(D1,"a1",... to
....,"a29"),CHOOSE(D1-29,"b1",... to ...,"b29")),"")

Finally dawned on me that first <=29 needed to be <=58... DUH!

Thanks for the help, always good stuff from you and this group.

Regards,
Howard

"Tom Ogilvy" wrote in message
...
=IF(AND(D1=1,D1<=6),IF(D1<=3,CHOOSE(D1,"A","B","C "),CHOOSE(D1-3,"D","E","F")),"")

in D1 successively enter the numbers 1 to 6 inclusive.

--
Regards,
Tom Ogilvy


"L. Howard Kittle" wrote in message
...
Hi Tom,

Could you please elaborate a bit on your formula? I'm not getting the
jest of it.

Thanks.
Regards,
Howard

"Tom Ogilvy" wrote in message
...
=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

--
Regards,
Tom Ogilvy

"dkingston" wrote in message
...
The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will
only
allow 29 values. I actually have 54 lists to choose from. Is there a
way
around this? I tried breaking my lists into 2 groups but Excel doesn't
allow
an IF function in the source field in data validation.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however,
this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 Data Valadation Allow List Source =A5:A7 OK

E1 Data Valadation Allow List Source
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) OK

If you have trouble getting it together, I can send you an example
workbook.

HTH
Regards,
Howard

"dkingston" wrote in message
...
i have data validation for a cell set to allow data from a list using
a
named
range. can i make the name of the range in the data validation
source
field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c
respectively
and want the drop down in E1 to display only 1 of the 3 lists
depending on
the value in cell D2 how do i set up the data validation field?