Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Tunbridge Wells, United Kingdom
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consolidating Spreadsheets (XP)

    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? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    We never really grow up, we only learn how to act in public.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Spreadsheets (XP)

    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

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Tunbridge Wells, United Kingdom
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Spreadsheets (XP)

    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.
    We never really grow up, we only learn how to act in public.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Spreadsheets (XP)

    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?
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Tunbridge Wells, United Kingdom
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Spreadsheets (XP)

    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!
    We never really grow up, we only learn how to act in public.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •