Group information from several worksheets into one worksheet

[FredDave]

I have 55 sheets, each sheet has 11 questions (one per column b5- k5 which
are exactly the same. Each sheet is for 1 person to enter details, entering a
number between 1 and 10 depending on the question. This is filled in weekly
over a 52 week period. The rows are dated for each monday

The 55 sheets are dated for Mondays only for 52 weeks i.e.
Row 6 1/3/10
Row 7 8/3/10
Row 8 15/3/10


Date Question1, question2 question3 etc
1/3/10 5 4 2 etc

I now have a master sheet. for the answers to 1 mondays answer
This sheet has the same question in the column, each row has the name for
each sheet

Name Question1 Question2 question3 etc
Sheet1 1 4 6 etc
Each row is column a name of each sheet so i have 55 rows representating 1
row from each sheet for a particular date.

So for that week we can see all the answers from each sheet for that week.

There is an easy way and have 52 sheets with each sheet giving the result
for that weeks answer.

But Is there a way that i can just change the date on the master sheet and
it will pick up the one row from the other 55 sheets for thats week answer?

If anyone could help i would be very appriciated,

Thankyou in advance

 

[Vijay]

In your master sheet in A1 put the date,
and then I presume that in A2 you have the heading as sheet name, in b2
heading as question1 and so on
In A3, A4, A5 your multiple sheets name are there
Put this formula in B3 and then drag right and down.
=VLOOKUP($A$1,INDIRECT($A3&"!"&"$A:$E"),COLUMN(),0)

Vijay