Data Validation

[Rlay002]

Hello All-

Few Questions about Data Validation

1) I have a conditional data valdation set up to display a different
selection list in the second data field based upon the answer the user
provided in the previous cell to another data validation selection list
containing on Yes or No.

Lets say the user selects yes in the first field and then selects an
option in the second field based upon their previous answer of yes but
then goes back and changes yes to No, how can I erase the response the
user selected in the second field when the selection list is updated.
Now, the previous answer stays even though that answer is not in the
updated selection list.


2)Is there a way to filter the contents of a In cell drop down using
data validation. My selection list includes 1000 values but I want to
eliminate some based upon the responses the user provides.

For example I am managing a facility inventory for 20 regions, each
region has 10 facilities, when I select region #12 in a previous cell
drop down, I woud like the cell drop down in the facility field to only
be populated with the 10 facilities in that region.

3) When I use a range to populate the cell drop down lists using data
valdation, how can I ignore null values. The entire range will not
always be populated and I see a list of many blanks.


Thanks in advance.

 

[Jeff]

For your first question, you could try this.
right-click on tab name, select 'view code', type this
(3 lines -no wrapping like in this message)

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Selection.Address = "$A$2" Then Selection.Offset(0, 1)
= ""
End Sub

Where "$A$2" is your first dropdownlist - this erases
the choice next to it (modify ...offset(0,1) to point to
your dependent list.

 

[Jeff]

Question #2:

You may want to read this excellent help.

http://www.contextures.on.ca/xlDataVal01.html

 

[Rlay002]

Jeff -

Thanks for your quick reply to Question 1.

My first selecton list comes in cell E20 (through E50), the dependen
DDB is right next to it at F20. I have adjusted your formula as such t
no avail.

I dont think I am getting the selection offset right. In this instanc
what should it be

 

[Jim]

There are files to download and detailed explanations of how to do this.
Look at the "Named Range" file at www.myweb.cableone.net/twodays and/or have
a look at www.contextures.com

Basically, let's say you have Data Validation allowing either Yes or No in
cell A2. Somewhere you would have choices you want to appear if the choice
is Yes. Get those choices in a range and name it Yes. Do the same for the No
responses.

In B2 use Data>Validation>Setting>Custom>=INDIRECT(A2). Once you make a
choice in A2, the appropriate list will appear.