stumped on creating dependent data validation lists

I created 2 Data Validation lists with Indirect functions. On trying
to use them in conjunction with a 3rd list, i realized my logic in
trying to accomplish what i need had been flawed. The problem is that
the 3rd list, which should be dependent on the 1st 2, changes
location.
That is, i have a very busy worksheet. In the main body of the
worksheet, there are no empty rows and columns. But almost every cell
needs to have dependent validation lists to select items from. The
main body is from A13 to AW131. In the area E14 to Q131, I have the
potential to need a dependent dropdown list in alomst every cell. i
tried putting the 1st 2 lists in an open area in rows 10 & 11, but i
soon discovered that the reference cells i entered in them using the
indirect function were not relative, and stayed the same no matter
which cell in the body needed to depend on them. After much work
creating named lists and working out the "indirect" formaulas, i
realized that when i selected the 1st list, ther was no way for Excel
to know which cell below to reference. I think what i want to do is
not possible, yet, i have over time received some incredible advise in
this group, so I figured that just because it seems impossbile to me,
it might be very possible to some of the users in this group. Or,
perhaps it can be accomplished with combo boxes?

Thanks,

Tonso

hi

take a look at
http://www.contextures.com/xlDataVal15.html

--
Regards
Roger Govier

Tonso wrote:
I created 2 Data Validation lists with Indirect functions. On trying
to use them in conjunction with a 3rd list, i realized my logic in
trying to accomplish what i need had been flawed. The problem is that
the 3rd list, which should be dependent on the 1st 2, changes
location.
That is, i have a very busy worksheet. In the main body of the
worksheet, there are no empty rows and columns. But almost every cell
needs to have dependent validation lists to select items from. The
main body is from A13 to AW131. In the area E14 to Q131, I have the
potential to need a dependent dropdown list in alomst every cell. i
tried putting the 1st 2 lists in an open area in rows 10 & 11, but i
soon discovered that the reference cells i entered in them using the
indirect function were not relative, and stayed the same no matter
which cell in the body needed to depend on them. After much work
creating named lists and working out the "indirect" formaulas, i
realized that when i selected the 1st list, ther was no way for Excel
to know which cell below to reference. I think what i want to do is
not possible, yet, i have over time received some incredible advise in
this group, so I figured that just because it seems impossbile to me,
it might be very possible to some of the users in this group. Or,
perhaps it can be accomplished with combo boxes?

Thanks,

Tonso