excelbytes
Active Member
- Joined
- Dec 11, 2014
- Messages
- 251
- Office Version
- 365
- Platform
- 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.
=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.