| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation - Multiple Dependent Lists | Excel Discussion (Misc queries) | |||
| Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
| Multiple Validation Lists | Excel Worksheet Functions | |||
| Printing Multiple Data Validation Lists | Excel Worksheet Functions | |||
| Excel data validation multiple lists | Excel Programming | |||
Linear Mode
