Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
rolling up several spreadsheets into one
I have approx 6 spreadsheets I need to merge into one document to receive a
final result. Each spreadsheet will be worked by a different indiviudual. The spreadsheets are being used to produce auditing scores for several areas and merged into one final soreadsheet rolling up all of the totals. I'm unsure at this point what is the best way to go about this. Any feedback is very appreciative. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
rolling up several spreadsheets into one
Try this:
You might be able to use MS Query to consolidate the Excel ranges from your multiple wkbks/wkshts. This also works for consolidating data from the active workbook (Just save it first so Excel can find it): This example uses 5 named ranges in 5 different workbooks. (Each range contains 4 columns: Dept, PartNum, Desc, Price) Assumptions: The data in each wkbk is structured like a table: ---Col headings (Dept, PartNum, Desc, Price) ---Columns are in the same order. The data in each wkbk must be in named ranges. ---I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc ---You may use the same range name in different wkbks. (Note: 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 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 * FROM `C:\Dept1111`.rng1111Data UNION ALL SELECT * FROM `C:\Dept2222`.rng2222Data UNION ALL SELECT * FROM `C:\Dept3333`.rng3333Data UNION ALL SELECT * FROM `C:\Dept4444`.rng4444Data UNION ALL SELECT * FROM `C:\Dept5555`.rng5555Data Return the data to Excel. Once that is done....to get the latest data just click in the data range then DataRefresh Data. (You can edit the query at any time to add/remove data sources and/or fields.) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "mmarshall@sagamore" wrote: I have approx 6 spreadsheets I need to merge into one document to receive a final result. Each spreadsheet will be worked by a different indiviudual. The spreadsheets are being used to produce auditing scores for several areas and merged into one final soreadsheet rolling up all of the totals. I'm unsure at this point what is the best way to go about this. Any feedback is very appreciative. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine information about products from 2 spreadsheets | Excel Worksheet Functions | |||
Spreadsheets are cut off when you paste and link? | Excel Worksheet Functions | |||
Excel Charts Linked to Spreadsheets | Charts and Charting in Excel | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions | |||
How do I get my actuarial spreadsheets to comply with SOx? | Excel Discussion (Misc queries) |