Validation rule but only sometimes

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

Microsoft Access has _MUCH_ better validation for this type of thing

Good luck

-Aaron

On Tue, 21 Jun 2005 20:22:43 -0500, aaron.kempf@gmail.com <aaron.kempf@gmail.com> wrote:

> Microsoft Access has _MUCH_ better validation for this type of thing
>
> Good luck
>
> -Aaron


Yeah, I figured that.

This was supposed to be a pretty simple spreadsheet,
and I just wanted to make a few cells have different
content depending on what other cells had in them.

It's not worth the time to switch to Access, and the
very few people who might ever see this won't want to
bother with Access. (It might not even be worth my
time doing it the way I'm heading, anyway. <grin>)

Mike

On Tue, 21 Jun 2005 20:26:31 -0500, Broida (spamless) <Broida@charter.not.net> wrote:

> On Tue, 21 Jun 2005 20:22:43 -0500, aaron.kempf@gmail.com <aaron.kempf@gmail.com> wrote:
>
>> Microsoft Access has _MUCH_ better validation for this type of thing
>>
>> Good luck
>>
>> -Aaron
>
>
> Yeah, I figured that.
>
> This was supposed to be a pretty simple spreadsheet,
> and I just wanted to make a few cells have different
> content depending on what other cells had in them.
>
> It's not worth the time to switch to Access, and the
> very few people who might ever see this won't want to
> bother with Access. (It might not even be worth my
> time doing it the way I'm heading, anyway. <grin>)

And I figured I might learn something useful
by asking here how to do it.

Mike

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
>