Thread: Retrieving values from Multiple worksheets

I have a large project, I am going to try to break down by sections, will greatly appreciate any thoughts ideas, suggestions, etc.

If I have a workbook with 5 worksheets, 1 an employee name list, and 4 others with an identical layout, the only difference is the cells mean something else (hours, name, production, average)but each cell directly relate to the identical cell in the other worksheets based upon column A "date", is it possible to return the values of each of these cells to another worksheet, based upon what and where the employees name is located?

More Detail:
Ok lets say I have 5 Lines of Production named Line A, Line B, Line C, Line D, Line E and on each line I have 5 workers (each in a Zone) Zone 1, Zone 2, Zone 3, Zone 4, Zone 5. I have 4 worksheets all layed out the same, with the Line and Zones going horizontally, and each date going vertically.
In the "Avg" Tab, Line C4 tells you what the average production was done on Line A, Zone 1 on 1/1/2011
In the "Production" Tab, Line C4 tells you what the total production was done on Line A, Zone 1on 1/1/2011
In the "Hours" Tab, Line C4 tells you the total hours worked on Line A, Zone 1on 1/1/2011
In the "Name" Tab, Line C4 tells you who worked on Line A, Zone 1on 1/1/2011


So you can see, in each worksheet cell C4 is directly related to each other. I would like to retrieve a collection of this information in another worksheet called "Data" based on inputting a date into the worksheet.

If you view the attachment, you will see I have a sample of what I would like done, based on the given information. I have highlighted the cells in each workbook, to help visualize what is happening and how the information is directly related.Each Color represents an employee, so we can see where that employee worked, as this will come in handy in part 2 of this project...

See the "Data" tab to see the results of what I am looking for...

Any thoughts how I can get this information to do what I want, is it possible? or is my head in the clouds?

Hi,

Add these formulas where indicated and autofill down:

DATA!B5 =INDEX(Avg!$B$4:$V$34,MATCH($D$2,Name!$B$4:$B$34,0),MATCH($A5,INDEX(Name!$B$4:$V$34,MATCH($D$2,Name!$B$4:$B$34,0),),0))

DATA!C5 =INDEX(Production!$B$4:$V$34,MATCH($D$2,Name!$B$4:$B$34,0),MATCH($A5,INDEX(Name!$B$4:$V$34,MATCH($D$2,Name!$B$4:$B$34,0),),0))

DATA!D5 =INDEX(Hours!$B$4:$V$34,MATCH($D$2,Name!$B$4:$B$34,0),MATCH($A5,INDEX(Name!$B$4:$V$34,MATCH($D$2,Name!$B$4:$B$34,0),),0))

Note - The formula is the same except for the initial worksheet reference.

Cheers,

Ok that works brilliantly, now to phase 2..

see an updated post...

00Able,

You appear to have deleted the spreadsheet attachment from your original post after you received an answer. To anyone else reading this thread, it leaves your question far more difficult to understand, and renders my response quite meaningless.

Please don't do that again. You might find the volunteers who provide their expertise here will be less inclined to consider your requests if this is the outcome.