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

[Gauthier]

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

 

[BrianB]

You may find that a pivot table will do the job using the option
"Muliple Consolidation Ranges".

You would probably have to hide sheets rather than have them deleted.
This way you could make one workbook & then copy.