Consolidating multiple sheets

[Guest]

I have over 70 sheets with the exact same layout which I want to consolidate.

There is a list of all the sheet names on my summary page in Cells L6:L77. I
tried using:
=SUM(INDIRECT("'"&L6:L77&"'!AV32"))
with AV32 being the first cell on each sheet I want to summarise.

But it only returns the amount from the first sheet.

Any ideas?

 

[Earl Kiosterud]

Phil,

Look at Data - Consolidate.

 

[Guest]

Which means adding each sheet reference 1 at a time....DOH !
I suppose I'll have to go with this if there's no formula that will do it.

Thanks.

 

[Ron Coderre]

While not my favorite technique, sometimes this approach saves me some time:

You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
--->Col headings (PmtAmt, DatePaid, etc),
--->Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in named ranges.
--->You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
--->Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then Data>Refresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron

 

[Guest]

That's quite an interesting approach actually which I could use elsewhere,
but........ the data in my case is in the same workbook so I can't use a
common named range.

I have come up with a workaround in the meantime though. It's a little
inefficient but it'll do the job !