Data validation question

[rjc4]

Hi All,

RangeA
Cell Y1 contains text of OPT1
Cell Y2 contains text of OPT2
RangeB
Cell Z4 contains text of USER1
Cell Z5 contains text of USER2

In M5 is a dropdown containing choices of YES or NO.

In H8, how can I use data validation to show me only range Y1:Y2 if M5 is YES and range Z4:Z5 if M5 is NO.

I guess it requires a custom formula in the data validation setup but I can't get it to work. Could anyone help please!

Thanks,
RC

 

[BJungheim]

Select Y1:Y2, go to Insert|Name|Define and call this range Yes.

Select Z4:Z5, go to Insert|Name|Define and call this range No.

Select Range H8, go to Data|Validation, select Lists in the first dropdown, and in the source box put =INDIRECT(M5).

This will change your data validation list based on what is in M5.

Keep in mind that this will not prevent someone from setting H8 and then changing M5.

 

[rjc4]

Thankyou very much BJungheim for your prompt solution. Fits the bill perfectly.

Kind regards,
RC

 

[RAM]

Here's another link to a similar problem: http://www.mrexcel.com/board2/viewtopic.php?t=170677&highlight=dependent Scroll down to RAM.

That, Jungheims answer and the link within my link will give you a fair amount of answers to handle more situations like this.

HTH

RAM

 

[rjc4]

Thank you RAM.

Very helpful indeed.
Cheers,
RC