Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Combining Workbooks/worksheets

    I thought that there was a feature in Excel that would combine all the data from several worksheets into a summary worksheet. The summary would have all the columns and rows that were unique.

    Example would be that there were quarterly sales on individual worksheets with the sales persons names. When the worksheets are combined, there would be each quarter and each sales person's name, even if they appeared only on one quarter's worksheet.

    I thought that the feature was "combine" but that seems to only work for one left column and one heading row. We are looking for a way to easily copy all the data from several workbooks. Copy and Paste, of course, works, but we were hoping to automate the process for monthly updates.

    Is a macro the only way to get this combined workbook?

  2. #2
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    You can use the Data Consolidate command (Excel 2007). Have all quarterly sales worksheets open and in a new file use the Data Consolidate to combine them. You can add references as needed.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks, Maria

    In Excel 2010 you can only use Consolidate with one top (heading) and/or one left column. These appear to be the only rows that are text. The consolidation of all the other data must use a function like SUM.

    We have several columns of text data, as well as numberic, that we need to consolidate.

    It looks like copy and paste is the only way to get all the data on one worksheet or one of our original ideas, an Access database table.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    BJG,

    If your worksheets are all laid out the same, e.g. Balance Sheets from different divisions/regions you can create a summary sheet by copying one of the sheets then using Sum formulas like this: =Sum(EasternDiv:WesternDiv!B10). So if your tabs are from Left to right Summary, EasternDiv, NorthernDiv, SouthernDiv, and WesternDiv and you place the Sum on the Summary sheet it will consolidate the values in B10 for all the Sheets physically between EasternDiv and WesternDiv.

    Note B10 contains 20 on each of the 4 divisional sheets.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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