Lookup a range of values in multiple spreadsheets

[Courtney1401]

Hi, hoping someone can help me out. I have about 120 invididual workbooks, each of which has a schedule in it with a rage of values for different dates. I'm trying to do a formula to summarise the information, so I've pulled all of the workbook names into one sheet and I then want to pull all the values for the next 12 months into one cell, the values for 1 to 5 years into another cell and the values over 5 years into another cell.

So, summary sheet has:

Worksheet ref--------< 12 months--------1 - 5 years--------> 5 years
A
B
C
D

And I need to pull summed values into the < 12 months, 1-5 years and > 5 years columns. In each worksheet I have for example:

In Worksheet A:
Jul-12 6543
Aug-12 8234
Sep-12 7123
etc.

So far, the formula I have is:

=(VLOOKUP($K$19,INDIRECT("'"&A30&"'!A:X"),9,0))

Where $K$19 = Jul-12
and A30 = the Worksheet ref (e.g. "A")

How do I change this formula so it will sum a number of values, i.e. so the above formula will lookup and sum all values for Jul-12 to Jun-13 (without doing + as I will have 48 values to sum in the next column 1-5 years)?

I hope this makes sense.

Thanks for your help.

[K m]

You may consider pulling all the information into one workbook, if that is possible. You could develop a macro that copies the data from all the different workbooks

If you have one workbook with external references to 120 other workbooks, the whole file will appear very sluggish
==========================
I'm not sure I understand what you are trying to do but if you want to pull data from different areas and sum them you could do the different VLOOKUPs on one area of the worksheet and sum them or you could try formulas that use arrays. Look at SUMPRODUCT and SUMIF functions

[Courtney1401]

Sorry I meant 120 worksheets not workbooks. Unfortunately its something I've inherited and I'm trying to make it simpler!

I've tried some SUMPRODUCT and SUMIF formulas but they don't seem to be working for me either.

[K m]

Courtney, perhaps uploading a sample workbook might be helpful

If there is a similar structure to all of the worksheets ie rows and column headers are the same and the worksheets are in date order, you could use the 3d capabilities of Excel to lookup and sum the data

[Courtney1401]

I've taken out the non-essential information and only included 5 of the worksheets as an example in the attached doc. Hopefully it all makes a bit more sense now.

Thanks again.

SchedulesJune12YE.xlsm

[K m]

The structure between worksheets is not the same and none of your data has column labels.

Need more information to be able to help