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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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,769
Messages
5,708,420
Members
421,568
Latest member
Huxley

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