Data Validation With List And Condition

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
115
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,868
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe...

Create a table like this (Table1)
GenderNo 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
Joined
Aug 23, 2010
Messages
16,868
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Dec 11, 2014
Messages
115
Office Version
  1. 365
Platform
  1. Windows
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!
 
Solution

Forum statistics

Threads
1,141,777
Messages
5,708,461
Members
421,571
Latest member
ChaosPup

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top