+ Reply to Thread
Results 1 to 3 of 3

Combining like worksheets into a single worksheet

  1. #1
    JAS
    Guest

    Combining like worksheets into a single worksheet

    I have a single XL file with multiple worksheets that have the same format
    (column headers). How do I combine all of the individual worksheets into a
    single cumulative worksheet?

  2. #2
    Richard O. Neville
    Guest

    Re: Combining like worksheets into a single worksheet

    Assume you have three sheets, A, B, and C, that you want to summarize in a
    fourth, D. Copy A and rename it as D; it will become your master sheet.
    Delete all the data from D, but preserve the column headers.

    In cell A2 of D, construct a cross reference to A, B, and C. Type =; switch
    to sheet B and click on A2, type +, switch to sheet C and click on A2, type
    + again, switch to sheet C and click on A2, then hit Enter (no + after the
    last entry).

    To spread the formula, click on A2 in sheet D. Move the mouse to the lower
    right corner of the cell and manipulate it until a black + appears. Then
    hold down the left mouse button and drag down Column A so you capture all
    the data in sheets A, B, and C. Let's say you stop at A25. Select cells
    A2-A25 (they should already be selected when you drag down). Get the black +
    to appear again, and this time drag to the right to fill columns B and C.

    "JAS" <[email protected]> wrote in message
    news:[email protected]...
    >I have a single XL file with multiple worksheets that have the same format
    > (column headers). How do I combine all of the individual worksheets into
    > a
    > single cumulative worksheet?




  3. #3
    Ron Coderre
    Guest

    RE: Combining like worksheets into a single worksheet

    You might be able to use MS Query to consolidate Excel ranges from your
    multiple wkshts, as long as you don't mind formulas being converted to
    static values in the consolidation area.

    This example uses 5 named ranges which can be in the same or different
    worksheets of the same workbook.

    Assumptions:
    The data in each wksht is structured like a table:
    --->Col headings (Dept, PartNum, Desc, Price, ...etc)
    --->Columns are in the same order.

    The data in each wksht must be in named ranges.
    --->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
    --->You can re-use the same range name if it is in different wkbks.
    Save the wkbk after creating the names so Excel can find it with the names.

    (Note: In the next steps, 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)Data>Import External Data>New Database Query
    >Databases: Excel Files


    Browse to one of The files, pick ONE 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:\MyWkbk`.rng1111Data
    UNION ALL
    SELECT * FROM `C:\MyWkbk`.rng2222Data
    UNION ALL
    SELECT * FROM `C:\MyWkbk`.rng3333Data
    UNION ALL
    SELECT * FROM `C:\MyWkbk`.rng4444Data
    UNION ALL
    SELECT * FROM `C:\MyWkbk`.rng5555Data

    (Note: the apostrophes in the SQL code ( ` )are located on the same key as
    the tilde (~) )

    Return the data to Excel.

    Once that is done....to get the latest data just click in the data range
    then Data>Refresh 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


    "JAS" wrote:

    > I have a single XL file with multiple worksheets that have the same format
    > (column headers). How do I combine all of the individual worksheets into a
    > single cumulative worksheet?


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1