Enter the data as below
Now, highlight A1, A2 as below.
Under Formulas -> Define name -> Define name
Give name as “Gender’ as below
Click Ok
Similarly highlight B1 to B3, and define name as “Male”
Similarly highlight C1, C2 and define name as “Female”
Click OK
In sheet2, give the data as below
Highlight whole Column A, Click Data Validation -> Click Data Validation
It will display below screen. Select List. Type =Gender as below
Click OK
Now, we need Name to be listed based on selection in Column A.
To do this, highlight the Column B2. Click Data -> Data
Validation -> Data Validation
Select List. Give the relative reference field name as A2. Use indirect function as below
Click Ok.
Click Ok (if any error popup, because, as of now A2 is not
having any value, and its blank)
Select Female in A2. Now, in B2, you will see the
corresponding values as below
Now, to apply the above validation in other columns in B, copy the column B2.
Highlight the columns from B3 to B13 as below
Right click. Click Paste Special as below
In below screen, click Validation as below
Click Ok.
Now, you can see in B3, as below
