| 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
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? |
|
#6
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? |
|
#7
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? |
| 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 | |||
Hybrid Mode
