Choose from a list depending on the value of another cell

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

Guest

Hi, there

I'd like to set the validation of cell c2 to choose from a list (c3:c5) if the value of b2 is equal to 'x'. How can I do that?

thanks in advance!!!!
 
You don't say what you want to do if B2 does not equal 'x', but this formula
in Data Validation, with an 'Allow' value of List works

=IF(B2="x",C3:C5,"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

RCYanez said:
Hi, there,

I'd like to set the validation of cell c2 to choose from a list (c3:c5) if
Click to expand...
the value of b2 is equal to 'x'. How can I do that??
 
Hi, Bob,

I tried it but it didn't work. This is what I did: I selected Data Validation on cell d64, chose "custom" on the "allow" drop down and then typed the following: =IF(C64="Project",D12:D23,""). What I would have liked is that if cell 64 contains the word "Project", then I would only be allowed to select from the list of values within the range d21 and d23. What actually happens is that there is no "drop down" buttom if c64 has the value Project, and it accepts any value I enter in d64.

To answer your question about what I would like to have if the value is not "Project", is "allow any value".

thanks again!!!
Rocío
 
Bob,

IGNORE MY PREVIOUS MESSAGE!!!! I missed the fcat that I should have had Allo List!!!


THANKS!!!!!
 
no, sorry, it does not work with Allow list. When you do that, there is no formula but a "source", which means that it only allows the values in a range and it does not accept a formula. Back to square one!!!!


HELP!!!!
 
It does work, I tested it!. This is bizarre what I am going to say, but
trust me it works (at least on XP pro, Xl2000 it does).

What I did was this
- set the Allow value to Custom
- typed in my formula as provided

This didn't work, and I realise I needed List as the Allow value, so
- I went back into DV and changed Custom to List

Voila it worked, for both x in B2 and empty.

A couple of things to note
- when you change Custom to List, you must have the value x in B2,
otherwise it fails
- the DV will use that list if B2 is x, but fails on any other value other
than empty - e.g. y will not allow any input at all

Try it and see if it works for you.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

RCYanez said:
no, sorry, it does not work with Allow list. When you do that, there is
Click to expand...
no formula but a "source", which means that it only allows the values in a
range and it does not accept a formula. Back to square one!!!!
 
Hi, Bob

Yes, it does (partially) work if you also check the in-cell dropdown. It does not allow you to update the formula, though. You need to repeat all the steps (first custom and then list, etc). Unfortunately, as you said, it does not allow any other value if b2 is not equal to x, which is not exactly what I want

would you know how to "trick it" into accepting any value?? (already tried defining another range for the false value and setting the validation on that other range to "any value", but it didn't work)

thanks, again!
 
In the Data Validation dialog box, on the Error Alert tab, remove the
check mark from 'Show error alert after invalid data is entered'

Users will only see the dropdown list if cell B2 contains an x, but can
type values that aren't in the list.
 
Problem with this suggestion is that it allows any value to be input even
when b2 is an 'x'.

My original suggestion allows just the values in C3:C5 if b2 is an 'x', and
any value if B2 is empty, is this not enough?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Debra Dalgleish said:
In the Data Validation dialog box, on the Error Alert tab, remove the
check mark from 'Show error alert after invalid data is entered'

Users will only see the dropdown list if cell B2 contains an x, but can
type values that aren't in the list.
Click to expand...
It does not allow you to update the formula, though. You need to repeat all
the steps (first custom and then list, etc). Unfortunately, as you said, it
does not allow any other value if b2 is not equal to x, which is not exactly
what I want.tried defining another range for the false value and setting the validation
on that other range to "any value", but it didn't work).
 
Debra, Bob,

First of all, HAPPY NEW YEAR!!!! Sorry that I didn't keep on replying, but it was getting close to my new year celebration in The Netherlands.

Back to the problem: Debra's suggestion has, indeed, the problem that "any" value is allowed even if b2 is "x". Bob's suggestion has the problem that it will only allow any value if b2 is empty. The actual example I have is that b2 will always have a value, but I only want the drop down list if and only if b2 = "x".

Is there a way to achieve this through validation or do I have to program something else (macros?)

thanks in advance
 
Back
Top