ExcelBanter - Rollup of Data in Multiple Sheets (based on user input)

OK...using excel 2000...here's what i have and need to do:
here's what i have set-up:
- one workbook containing 12 sheets
- each sheet is exactly the same
- database range in each sheet is A14:L27
- column A contains WEEK info (ie...Week 1, Week 2, Week 3, etc. to Week 13)
- have a SUMMARY sheet at the end of the workbook
- the SUMMARY sheet contains a cell which reads: Enter Week # (i've created
a dropdown list to avoid user input error)
here's what i want to do:
- i want the user to choose the week (as stated above) - for this example,
lets say they chose WEEK 1
- then i want all the data, in rows beginning with week 1, from each
database range, in each sheet (1-12) to be rolled up onto the summary
- in addition, at the bottom of COLUMN D, i want to add a TOTAL...(keep in
mind, rows will vary...although there are 12 sheets, the user may only
require 8 - but that'll be my next question - if the user does not require
all 12 sheets (essentially, each sheet represents data for each of their
employees...it will vary with each user)...so if the user decides to delete
a couple of sheets, i would like the roll-up to be flexible ie..perhaps the
solution/code would look to see how many sheets there are, or "go to the
next sheet - if none, then stop macro and end it there" (type thing?!)

i thought about using advanced filtering? could i use advanced filtering
with multiple ranges?
can anyone suggest how i should go about doing this?

appreciate your help!
sandi