Debra Dalgleish has some tips for working with Data|Validation and dependent
lists at:
http://www.contextures.com/xlDataVal02.html

"Broida (spamless)" wrote:
>
> Well, that's -almost- what I need. Maybe I should have given
> more detail. Here's what I have (modified for simplicity).
>
> A1 is a data|validation list dropdown that the user can select
> one of three things from (stored over in R1:R3).
> IF the user selects XX in A1, then I want B1 to be -another-
> dropdown he can select one of two items in (stored in S1:S2).
> But if the user selected YY in A1, then I want B1 to be a
> single text item; no user choice in B1.
> And if the user selected ZZ in A1, then I want B1 to be empty;
> again no user choice in B1.
>
> So, what I need in B1 is:
>
> if (a1="XX", be_a_dropdown, if(a1="YY","singletext",""))
>
> But I don't know how to encode "be_a_dropdown" to CHANGE B1
> from a formula to a data|validation list dropdown in that one
> case. I don't want the user to have to select something
> somewhere ELSE to set it up, either.
>
> I've already cheated with a "yes"/"no" dropdown elsewhere by
> changing the list behind it to "yes"/"yes" in one case.
> If that's the only way, I could do something similar here,
> changing the contents of S1:S2, but that won't look as nice
> as what I'm trying to get to.
>
> Maybe I might have to get into some actual VBA coding to
> change B1 when A1 is filled. That's beyond me right now,
> but I can learn fast if it's the only option.
>
> Mike
>
> On Tue, 21 Jun 2005 20:51:05 -0500, Dave Peterson <ec35720@netscapeXSPAM.com> wrote:
>
> > I'd use a helper cell.
> >
> > Say A1 has the value to check
> > B1 has the data|validation list
> > Then in C1, I'd use:
> >
> > =if(a1="x","yourvalue",b1)
> >
> > Then use C1 in any calculation/formula later.
> >
> >
> >
> > "Broida (spamless)" wrote:
> >>
> >> Hi!
> >> Thanks to help here, I now know how to set a cell to
> >> allow the user to select items from a list elsewhere
> >> in a spreadsheet. Data->Validation->List works great!
> >>
> >> But now I'd like to expand that. I want to make that
> >> cell be a dropdown list SOMETIMES, but other times
> >> force a specific value (or nothing) into it, all based
> >> on the value of another cell.
> >>
> >> If I put in a validation rule, there's no provision
> >> for doing an "if(x,y,z)" where either y or z makes a
> >> dropdown list while the other one does not.
> >>
> >> How can I do this?
> >>
> >> Mike
> >

--

Dave Peterson