Excel dynamic matching

hiya,

i have three sheets:

sheet 1 - detail is input manually someone's name and personal details

sheet 2 - a sheet that takes some detail from sheet 1, and will be populated live from the next sheet.

sheet 3 - is contained in a template 'blank' folder full of all the forms and files we need for every customer. once sheet 1 is completed, this template, copied and renamed as per cell 1 in sheet 1.

how do i make sheet 2 vlookup the relevant detail? in particular how it can use the detail in cell 1, sheet 1, to look in the usual location for the right worksheet?

=vlookup($a$3, 'c:\users\desktop\newhire\ ****"name in cell"****!A1:F7,4,false)
i cn seem to get the address in the vlookup to become dynamic, and its doing my head in.