Clearing Data Validation

[MonkeyDance1]

I currently have 2 data validation drop down lists in cells A1 and B1. The range name lists that populate in B1 are contingent upon the selection made in A1. Now, there are a few option in the A1 list that I do NOT want a list to generate at all in B1, however, I cannot add a "" to the end of the formula is I get an error message. What is the best way to clear the list in B1 if all but two different options are selected from A1???

I tried creating a fake range name of blank cells but that doesn't work either and I can't hide the row based on the selection in A1 because content exists to the right of A1 and B1.

Thanks so much! Love this forum.

 

[Gerald Higgins]

What formula are you using to populate B1 ?

 

[MonkeyDance1]

It's a basic IF A1 = "____" Then (range name of list 1), IF A1 = "_____" Then (range name of list 2), _______ <~~ And this last part is my issue. I need it to yield a blank value since none of the other options of A1 require a second drop down option.

 

[Gerald Higgins]

It would really help me if you would post the actual formula.

 

[MonkeyDance1]

Sorry about that. See below:

=IF($E$9="InSitu",InSitu,IF($E$9="Open Pit Mining",Openpit, ? ))

Where the ? exists, is where I get an error message saying it can't be a null value, etc. It would also be nice to find a method to where when an option is selected in $E$9, that the second list resets back to the first option on the list.

 

[Gerald Higgins]

So let me get this straight - that formula - is it the one that is actually in use in the Data Validation Dialog Box ?

I've tried this version . . .
=IF($E$9="InSitu",InSitu,IF($E$9="Open Pit Mining",OpenPit,""))
and it seems to work the way I think you want it to.

I.e. if E9=Insitu, options in the drop down box are the contents of the range called InSitu.
If E9=Open Pit Mining, options in the drop down box are the contents of the range called OpenPit.
If E9=blank, no options are shown in the drop down box, but any value is allowed to be entered.
If E9= something other than Insitu or Open Pit Mining, no options are shown in the drop down box, and no values can be entered.