Summary of multiple worksheets

[Anto111]

Hi everyone,

I have a summary sheet to summarize data that is contained in a seperate
workbook.

The seperate workbook contains a number of worksheets named week 1 : week 20
all with exactly the same layout but containing different data for each week
of a sports season. Along the top of each worksheet is a header for each day
of the week, along the side is a person name.

At the moment on the summary sheet I have the formula:

IF($O$2="Monday",VLOOKUP($F$2,'[Heart Rate.xlsx]Week
1'!$C$7:$NF$372,9,FALSE),IF($O$2="Tuesday",VLOOKUP($F$2,'[Heart
Rate.xlsx]Week
1'!$C$7:$NF$372,55,FALSE),IF($O$2="Wednesday",VLOOKUP($F$2,'[Heart
Rate.xlsx]Week
1'!$C$7:$NF$372,101,FALSE),IF($O$2="Thursday",VLOOKUP($F$2,'[Heart
Rate.xlsx]Week
1'!$C$7:$NF$372,147,FALSE),IF($O$2="Friday",VLOOKUP($F$2,'[Heart
Rate.xlsx]Week
1'!$C$7:$NF$372,193,FALSE),IF($O$2="Saturday",VLOOKUP($F$2,'[Heart
Rate.xlsx]Week
1'!$C$7:$NF$372,239,FALSE),IF($O$2="Sunday",VLOOKUP($F$2,'[Heart
Rate.xlsx]Week
1'!$C$7:$NF$372,285,FALSE),IF($O$2="Average",VLOOKUP($F$2,'[Heart
Rate.xlsx]Week 1'!$C$7:$NF$372,331,FALSE)))))))))

This returns data for a person when the persons name is in F2 and the day is
in O2.

At the moment I am resorting to using one of these for each weekly worksheet
and changing the formula accordingly. What I would like to do is use just one
summary sheet that returns the current data but adds a further dimension by
returning this data according to the week designated in another cell, in a
similar fashion to the way I have used person name / weekday.

Sorry for the long winded explanation,

and many thanks in advance for your time.

Kind regards,

Ant