Data Validation with dynamic ranges
 

I asked this question before Christmas but never managed to fully resolve it
(or find a good workaround), so I'll ask now and try to be clearer about it
this time. Basically I have one dynamic named range* named Choice, and then
several dynamic ranges named List1, List2, List3 etc...

In my sheet, I want to use Data Validation to ask the user to select a value
from a drop-down list containing the values in range Choice, and then, if the
user has chosen the seventh choice in range Choice, I want to the user to
select a value from a drop-down list in a neighboring cell containing the
values in range List7.

So, in Cell A1 (for example) I want to pick from any choice in range Choice
through using Data Validation; in Cell A2 I want to have the data validation
reflect the choice made in A1. The first part works fine, but I can't think
of a way to get the second part working. I've learned that if I'm not using
dynamic ranges I can use =INDIRECT() in the data validation to solve this,
but I haven't made it work with a dynamic range.

Please help me if you can fix this, or if you can think of a smart
workaround. Thanks!









* Using the formula =OFFSET($K$2;0;0;MATCH("*";$K:$K;-1)-1;1) in a name

Data Validation with dynamic ranges
 

I find this page describe how to use INDIRECT with dynamic ranges very well.

http://www.contextures.com/xldataval02.html
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Babymech" wrote:

I asked this question before Christmas but never managed to fully resolve it
(or find a good workaround), so I'll ask now and try to be clearer about it
this time. Basically I have one dynamic named range* named Choice, and then
several dynamic ranges named List1, List2, List3 etc...

In my sheet, I want to use Data Validation to ask the user to select a value
from a drop-down list containing the values in range Choice, and then, if the
user has chosen the seventh choice in range Choice, I want to the user to
select a value from a drop-down list in a neighboring cell containing the
values in range List7.

So, in Cell A1 (for example) I want to pick from any choice in range Choice
through using Data Validation; in Cell A2 I want to have the data validation
reflect the choice made in A1. The first part works fine, but I can't think
of a way to get the second part working. I've learned that if I'm not using
dynamic ranges I can use =INDIRECT() in the data validation to solve this,
but I haven't made it work with a dynamic range.

Please help me if you can fix this, or if you can think of a smart
workaround. Thanks!









* Using the formula =OFFSET($K$2;0;0;MATCH("*";$K:$K;-1)-1;1) in a name

Data Validation with dynamic ranges
 

Apply Vlookup to this validation will work
--
Thanks
Suleman Peerzade


"Babymech" wrote:

I asked this question before Christmas but never managed to fully resolve it
(or find a good workaround), so I'll ask now and try to be clearer about it
this time. Basically I have one dynamic named range* named Choice, and then
several dynamic ranges named List1, List2, List3 etc...

In my sheet, I want to use Data Validation to ask the user to select a value
from a drop-down list containing the values in range Choice, and then, if the
user has chosen the seventh choice in range Choice, I want to the user to
select a value from a drop-down list in a neighboring cell containing the
values in range List7.

So, in Cell A1 (for example) I want to pick from any choice in range Choice
through using Data Validation; in Cell A2 I want to have the data validation
reflect the choice made in A1. The first part works fine, but I can't think
of a way to get the second part working. I've learned that if I'm not using
dynamic ranges I can use =INDIRECT() in the data validation to solve this,
but I haven't made it work with a dynamic range.

Please help me if you can fix this, or if you can think of a smart
workaround. Thanks!









* Using the formula =OFFSET($K$2;0;0;MATCH("*";$K:$K;-1)-1;1) in a name