Problem:

I need to use validate entry data using a series of dependent lists. For
example, in one instance the first Field (Field-A) is for Body Part (e.g.
UpperArm or Abdomen), which are stored in a series of vertical cells (List-A).

For each possible Field-A entry, there will be a series of possible Field-B
entries (for the muscles associated with that Body Part. For example
UpperArm would allow (among others) Biceps Brachii, Triceps Brachii,
Coracobrachialis, etc. and for Abdomen the possibilities would include Rectus
Abdominus, Oblique, etc.

As long as I've used defined-size Ranges on the Lists (e.g. A1:A12), I have
had no problem making the system work perfectly. In other words, I
understand that the entries on List-A must perfectly match the Range Names
for the dependent lists. Again, using defined-size ranges, the whole system
works perfectly.

The problem is that I want the ability to add an unlimited number of Field-B
possibilities (OK, there aren't an unlimited number of muscles in the body,
but I'm using this system for other things as well). So, I've been trying to
use Dynamic Range to define each List-B. Using EXACTLY the same Range Names
(re-defining each as a Dynamic Range), I run into the problem. When I click
in Field-B, I no longer see the appropriate List-B for each Field-A entry.
Instead, I see a blank drop-down.

As a point of bug-checking, the Dynamic Ranges themselves work perfectly.
If I use a test cell, and do Data Validation directly on the Dynamic Range,
the drop down list matches the Dynamic Range perfectly.

I am far, far, far from being an expert on Excel 2003, but I have come to
the conclusion that Data Validation via Dependent Lists defined by Dynamic
Range simply will not work. Am I correct?

If I am mistaken, where am I going wrong?

Lee

There are instructions for using dynamic ranges at the end of this page
on dependent lists:

http://www.contextures.com/xlDataVal02.html

Lee4 wrote:
Problem:

I need to use validate entry data using a series of dependent lists. For
example, in one instance the first Field (Field-A) is for Body Part (e.g.
UpperArm or Abdomen), which are stored in a series of vertical cells (List-A).

For each possible Field-A entry, there will be a series of possible Field-B
entries (for the muscles associated with that Body Part. For example
UpperArm would allow (among others) Biceps Brachii, Triceps Brachii,
Coracobrachialis, etc. and for Abdomen the possibilities would include Rectus
Abdominus, Oblique, etc.

As long as I've used defined-size Ranges on the Lists (e.g. A1:A12), I have
had no problem making the system work perfectly. In other words, I
understand that the entries on List-A must perfectly match the Range Names
for the dependent lists. Again, using defined-size ranges, the whole system
works perfectly.

The problem is that I want the ability to add an unlimited number of Field-B
possibilities (OK, there aren't an unlimited number of muscles in the body,
but I'm using this system for other things as well). So, I've been trying to
use Dynamic Range to define each List-B. Using EXACTLY the same Range Names
(re-defining each as a Dynamic Range), I run into the problem. When I click
in Field-B, I no longer see the appropriate List-B for each Field-A entry.
Instead, I see a blank drop-down.

As a point of bug-checking, the Dynamic Ranges themselves work perfectly.
If I use a test cell, and do Data Validation directly on the Dynamic Range,
the drop down list matches the Dynamic Range perfectly.

I am far, far, far from being an expert on Excel 2003, but I have come to
the conclusion that Data Validation via Dependent Lists defined by Dynamic
Range simply will not work. Am I correct?

If I am mistaken, where am I going wrong?

Lee


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

So, since the Field-A validation will not use INDIRECT, Range-A can indeed by
a Dynamic Range, but Field-B will have to use the work-around described on
your Link, correct?

If so, I think I've got it!

"Debra Dalgleish" wrote:

There are instructions for using dynamic ranges at the end of this page
on dependent lists:

http://www.contextures.com/xlDataVal02.html

Lee4 wrote:
Problem:

I need to use validate entry data using a series of dependent lists. For
example, in one instance the first Field (Field-A) is for Body Part (e.g.
UpperArm or Abdomen), which are stored in a series of vertical cells (List-A).

For each possible Field-A entry, there will be a series of possible Field-B
entries (for the muscles associated with that Body Part. For example
UpperArm would allow (among others) Biceps Brachii, Triceps Brachii,
Coracobrachialis, etc. and for Abdomen the possibilities would include Rectus
Abdominus, Oblique, etc.

As long as I've used defined-size Ranges on the Lists (e.g. A1:A12), I have
had no problem making the system work perfectly. In other words, I
understand that the entries on List-A must perfectly match the Range Names
for the dependent lists. Again, using defined-size ranges, the whole system
works perfectly.

The problem is that I want the ability to add an unlimited number of Field-B
possibilities (OK, there aren't an unlimited number of muscles in the body,
but I'm using this system for other things as well). So, I've been trying to
use Dynamic Range to define each List-B. Using EXACTLY the same Range Names
(re-defining each as a Dynamic Range), I run into the problem. When I click
in Field-B, I no longer see the appropriate List-B for each Field-A entry.
Instead, I see a blank drop-down.

As a point of bug-checking, the Dynamic Ranges themselves work perfectly.
If I use a test cell, and do Data Validation directly on the Dynamic Range,
the drop down list matches the Dynamic Range perfectly.

I am far, far, far from being an expert on Excel 2003, but I have come to
the conclusion that Data Validation via Dependent Lists defined by Dynamic
Range simply will not work. Am I correct?

If I am mistaken, where am I going wrong?

Lee


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Sounds like you've got it!

Lee4 wrote:
So, since the Field-A validation will not use INDIRECT, Range-A can indeed by
a Dynamic Range, but Field-B will have to use the work-around described on
your Link, correct?

If so, I think I've got it!

"Debra Dalgleish" wrote:


There are instructions for using dynamic ranges at the end of this page
on dependent lists:

http://www.contextures.com/xlDataVal02.html

Lee4 wrote:

Problem:

I need to use validate entry data using a series of dependent lists. For
example, in one instance the first Field (Field-A) is for Body Part (e.g.
UpperArm or Abdomen), which are stored in a series of vertical cells (List-A).

For each possible Field-A entry, there will be a series of possible Field-B
entries (for the muscles associated with that Body Part. For example
UpperArm would allow (among others) Biceps Brachii, Triceps Brachii,
Coracobrachialis, etc. and for Abdomen the possibilities would include Rectus
Abdominus, Oblique, etc.

As long as I've used defined-size Ranges on the Lists (e.g. A1:A12), I have
had no problem making the system work perfectly. In other words, I
understand that the entries on List-A must perfectly match the Range Names
for the dependent lists. Again, using defined-size ranges, the whole system
works perfectly.

The problem is that I want the ability to add an unlimited number of Field-B
possibilities (OK, there aren't an unlimited number of muscles in the body,
but I'm using this system for other things as well). So, I've been trying to
use Dynamic Range to define each List-B. Using EXACTLY the same Range Names
(re-defining each as a Dynamic Range), I run into the problem. When I click
in Field-B, I no longer see the appropriate List-B for each Field-A entry.
Instead, I see a blank drop-down.

As a point of bug-checking, the Dynamic Ranges themselves work perfectly.
If I use a test cell, and do Data Validation directly on the Dynamic Range,
the drop down list matches the Dynamic Range perfectly.

I am far, far, far from being an expert on Excel 2003, but I have come to
the conclusion that Data Validation via Dependent Lists defined by Dynamic
Range simply will not work. Am I correct?

If I am mistaken, where am I going wrong?

Lee


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com