Validation rule but only sometimes

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

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
>

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
>

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

Thanks!
I'll check there.


On Fri, 24 Jun 2005 05:53:08 -0500, Dave Peterson <ec35720@netscapeXSPAM.com> wrote:

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