Data Validation With List And Condition

[excelbytes]

I am trying to create a scenario whereby the user can't enter anything into a cell with a data validation unless something is entered into another cell. For example, there is a drop down in column B with various selections. If nothing is selected in column B, say cell B2, then nothing can be entered in cell F2. IF something is entered in B2, then a drop down list would appear in F2 with the choices of only MALE or FEMALE. I don't know how to combine both a formula and a drop down list, so my solution was to create two tables: "gender", which has "MALE" and "FEMALE" options and "no_ender" which has the option of "X". Then I would enter the following formula in the data validation in in F2:

=IF(LEN(B2)=0,INDIRECT("no_gender[NoGender]"),INDIRECT("gender[Gender]"))

I also tried:

=IF(B2="",INDIRECT("no_gender[NoGender]"),INDIRECT("gender[Gender]"))

I also tried:

=IF(LEN(B2)>0,INDIRECT("gender[Gender]"),INDIRECT("no_gender[NoGender]"))

In every case, the correct drop down list appears based on the entry or no entry in B2. The data validation error alert shows "Stop". HOWEVER, when there is something in B2, and MALE/FEMALE options appear in the drop down in F2, I can only choose one of those two. It won't let me type anything else. BUT, when there is nothing in B2, the drop down list does only shows "X", but I can type anything in that cell without an error. And as you can see by my various formulas, it doesn't matter which order I have the tables in the formula.

Any ideas as to why this is happening and how to solve it?

Thanks in advance for your time.

 

[Marcelo Branco]

Maybe...

Create a table like this (Table1)

Gender	No Gender
MALE
FEMALE

Data Validation
List
Source =IF(B2<>"",INDIRECT("Table1[Gender]"),INDIRECT("Table1[No Gender]"))

You can add a help message to the users
"To enter a proper gender in F2 the cell B2 cannot be blank"

Hope this helps

M.

 

[Marcelo Branco]

oops...
Sorry it's not working

M.

 

[Marcelo Branco]

In range, say, H1:I3

Pasta2
	H	I
1	Gender	No Gender
2	MALE	Error - Try again
3	FEMALE
Plan3

Cell F2
Data Validation > List
Uncheck Ignore blanks
Source: =IF(B2<>"",$H$2:$H$3,$I$2:$I$3)

M.

 

[excelbytes]

Actually, I was able to leave the previous structure that I had in place, but just uncheck "Ignore blanks" and it worked! I need to understand that option better. Thanks!