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

INDEX/MATCH explanation
[SOLVED] Match two cells composed of text in the same row