Excel Fomulas to gather data from multiple spreadsheets

[edwardporras]

I wonder if there is a formula I can use to gather data from different spreadsheets into one. I am trying to collect expense data from 2009 to 2011. from different departments. For example, I will have a summary on top and below are the different department expenses in details. all these data comes from different spreadsheets. Below is what I ma trying to collect. I am usin excel 2010


Expenses FY09 FY10 FY11 *FY12
Corporate 178,169.73 329,717 276,910 -
Labor 145,566.55 225,317 228,681 -
Travel 7,683.95 5,996 8,349 -
Transportation 495.24 1,534 1,010 -
Utilities 50.35 287 115.01 -
Printing (253.58) 3,158 177.29 -
Contracts 23,272.32 90,347 36,155 -
Training - 342 336.75 -
Supplies 1,354.89 2,650 2,092 -
Equipment - 86 (6.88) -
Interest - 23 10.14 -
J1 6,569 6,673 7,967
Labor 4,822 5,358 7,207
Travel 149 201 182
Transportation 15 8 (13)
Utilities - - -
Printing 5 - -
Contracts 1,493 948 545
Training - -
Supplies 85 158 46
Equipment - -
Interest 0.05 -
DHRS 104,497 119,288 114,377
Labor 90,371 103,005 103,895
Travel 5,331 1,331 3,307
Transportation 435 949 795
Utilities - - -
Printing (302) 3,066 20
Contracts 7,776 10,095 5,658
Training - -
Supplies 884 841 702
Equipment - -
Interest 0.09 0

Thanks for your help

[darkyam]

I don't understand the problem from your example workbook. There's only one sheet with any data. To best help you, we'll need to see the exact layout you're using (with dummy data).

As for a solution, my first thought is that this is possible with either VBA or an Indirect/Index/Match combination. Both have their advantages and disadvantages. VBA cannot be undone with a simple Ctrl+z if the macro is run in error or you want to check to see what the effects were before. It can also be more difficult to debug/change and, if someone disables macros, your sheet won't work. The formula route doesn't have these weaknesses, but since Indirect is a volatile function, the sheet will recalculate with every change you make to the workbook, potentially slowing it down if the sheet is large and complex. Also, the tabs will have to exactly match the row headings for Indirect to work.