Validation rule but only sometimes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Microsoft Access has _MUCH_ better validation for this type of thing

Good luck

-Aaron
Click to expand...


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
 
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>)
Click to expand...

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.
 
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.
Click to expand...
 
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
 
Back
Top