To Create dependent dropdown - using indirect function

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