Say in sheet 2 I have name, age, sex, address.
And tn sheet 1 on choosing a name Ram in column B in list box(data validation) I should see dynamic text in Column C, D, E with Age, sex and address info respectively. After sometime in column B if I choose Sita then her info should change in C, D ,E. How to come up with a solution for this..

Is macro required or can we do in excel itself and how? Help me with sample codes..

No need for a macro. This is done all the time with simple lookup formulas.

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples (http://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/system/app/pages/search?q=INDEX+MATCH&scope=search-site)
INDEX/MATCH explanation
[SOLVED] Match two cells composed of text in the same row (http://www.excelforum.com/excel-general/676832-match-two-cells-composed-of-text-in-the-same-row.html#3)