Prev: formatting for mail merge
Next: Suggestion on format of workbook
From: Tonso on 15 Apr 2010 16:14
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
From: Roger Govier on 15 Apr 2010 16:31
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
 | 
Pages: 1
Prev: formatting for mail merge
Next: Suggestion on format of workbook