Need help on drop down lists of two rows of a column - SOLVED

[Praneeth30]

I have two rows (1&2) of col A in which row1 has three possible values which user will select using drop down.

Based on the value selected by user in row1, row2 should be displayed with its corresponding values.

Example:
Row1 has three possible values Apple, Orange, Banana
Row2 can have Boy, girl, Both

If i select Apple from dropdown in Row1, row2 should automatically populate with Boy and if row1 is selected with orange row2 should be populated with girl.

Appreciate any help on how to achieve this using excel formulaes or any other way and not VBA.

 

[Luke M]

Regular dependent validation:
http://www.contextures.com/xlDataVal02.html

If you want to get fancy w/ macros:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

 

[jeffreyweir]

...or if you want to get fancy without macros:
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

 

[Praneeth30]

I have used OFFSET() and it worked perfectly. Thanks Luke and Jeffrey..
Can someone mark this a solved.