Excel 2007 : Auto-populate data - seems complicated.

Can anyone help me and let me know if this is possible? If so, what formula should I use?
I am a noob at this. So I'd like to apologize if really need guidance.

I am creating an excel file to be used at work as I'm trying to help out one of the teams with this.

Some of the fields have drop down lists already but what I would like to happen is this: The resource field will be in a drop down list and once I pick the name of the resource, I want the other fields to be auto populated based on the data for that specific resource which can be found in sheet 2.

i.e. I chose John Smith as a resource. Once it has been chosen, I'd like the fields D8, F5 and G5 to auto populate according to the data in sheet 2 which looks something like the one below.

Row 1 Row 2 Row 3
John Smith Basketball Team Center

So basically, B1 (Drop down list of resource names) will find the name in the list in sheet 2, C2:C70 then once it finds the same name, the following will automatically happen:

1. the cell in column D in the same row in sheet 2 as the chosen resource name will auto-populate D8
2. the cell in column B in the same row in sheet 2 as the chosen resource name will auto-populate G5
3. the cell in column A in the same row in sheet 2 as the chosen resource name will auto-populate F5.

I desperately need your help guys. I hope you can help me with this.

I have attached a sample excel file.

Thanks.