Consolidating Spreadsheets (XP)

Topic

New Reply

I have a user who wants to import the data from several excel files into one. If he tries to Import External Data, only selected fields from a spreadsheet are imported. Insert | Object lets me select an excel file, but only imports the first sheet.

Apart from laboriously copying and pasting the data, is there an easy way to do this?

Reply | Quote

Replies

You can try CONSOLIDATION (I have found little need for this, and do NOT use) This assumes you have “identical PLACEMENT of info on ALL THE BOOKS. You consolidate using 3D references.

For more info look in HELP – index tab
“Getting Results – Consolidate”
“Consolidate data” (or any of the other items)

If you want to consolidate DIFFERENT layouts, you need to use named ranges (the same name in all the worksheets for the same info)

You can also use pivot tables across multiple sheets, though I have found this not very intuitive and very format dependent

Hope this helps, if you have more questions, post again. The more specific the question the more specific the answer.

Otherwise copying all the sheets together and cut and pasting seems to be the answer. If this is a one time deal no problem, if it is a regular occurence, a good macro would be appropriate, but we don’t have anywhere near enough info to even make suggestions on how to do it.

Steve

Reply | Quote

I think the spreadsheets being copied may be quite disparate, in terms of content and layout. I think the idea is that all the data in several files needs to be available to the same person at one time. I thought Query might be getting me close, until it asked me to create links between sheets. We have come to the conclusion that the answer to this support request is, ‘No.’

If anyone can think of a way to turn a ‘no’ into a ‘yes’, I would welcome any suggestions.

Reply | Quote

Are you just trying to combine all of the sheets from multiple workbooks into one workbook by creating a copy of each sheet in the new workbook? If so, this could be done with a macro which would not be too difficult to write. Just need answers to a couple of questions:

1- Do you want to copy all sheets, including charts?

2- How do you want to name the sheets in the new workbook? If you want them named the same as the sheet being copied, what do you want to do with duplicate names?

3- How can the macro identify what workbooks to combine?

Reply | Quote

It’s a bit of a one-off task, as far as I know, so probably not worth the effort of writing a macro. In fact, he’s probably finished the task the hard way, by now!

Reply | Quote