Linking multiple tabs to one main data sheet

Guys,

I need some urgent help please. At work I need to collate project information contained in multiple data capture templates, into an overall data sheet so that I can pivot the data for some reports.

I will have up to 20 separate project data capture templates pasted into separate tabs in one work book. I'm really stuck on the most efficient technique to use which will automatically look across all the separate project data tabs and extrct each project data value into the appropriate heading (Yellow field in the attached spreadsheet) in a master data sheet.

FYI - I will need to run this report regularly with a fresh set of project templates, so this will not be a one off exercise.

Attached is an quick and dirty spreadsheet example that highlights what I'm trying to do. Please note that I'm using Excel 2002 at work.

Any speedy offers of help would be greatly appreciated

Thank you

Like so...make sure the column A references exactly match the sheet names, then the formulas in row 5 can be copied down...

Thanks for the feedback. If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.