Data Validation With List And Condition

excelbytes

Board Regular
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?

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Marcelo Branco

MrExcel MVP
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

MrExcel MVP
oops...
Sorry it's not working

M.

Marcelo Branco

MrExcel MVP
In range, say, H1:I3
Pasta2
HI
1GenderNo Gender
2MALEError - Try again
3FEMALE
Plan3

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

M.

excelbytes

Board Regular
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!

Replies
5
Views
487
Replies
6
Views
381
Replies
19
Views
1K
Replies
3
Views
167
Replies
0
Views
124

1,181,590
Messages
5,930,774
Members
436,760
Latest member
robgreen43

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back