If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Consolidating multiple sheets
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? -- http://www.redbrick.dcu.ie/~pele |
#2
|
|||
|
|||
Phil,
Look at Data - Consolidate. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Phil Osman" wrote in message ... 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? -- http://www.redbrick.dcu.ie/~pele |
#3
|
|||
|
|||
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. -- http://www.redbrick.dcu.ie/~pele "Earl Kiosterud" wrote: Phil, Look at Data - Consolidate. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Phil Osman" wrote in message ... 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? -- http://www.redbrick.dcu.ie/~pele |
#4
|
|||
|
|||
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)DataImport External DataNew 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 DataRefresh 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 |
#5
|
|||
|
|||
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 ! -- http://www.redbrick.dcu.ie/~pele "Ron Coderre" wrote: 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)DataImport External DataNew 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 DataRefresh 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking References from Multiple Sheets to One Summary Sheet | Kim | Setting up and Configuration | 3 | May 5th, 2005 04:56 PM |
Selecting Multiple Sheets | Dolphinv4 | General Discussion | 1 | September 2nd, 2004 10:21 PM |
Append data from multiple sheets | Terri | General Discussion | 1 | June 18th, 2004 04:57 PM |
Vlookup across multiple sheets | Brian | Worksheet Functions | 6 | March 10th, 2004 11:05 PM |
sorting data on multiple sheets | jer | Worksheet Functions | 5 | September 30th, 2003 12:02 AM |