+ Reply to Thread
Results 1 to 2 of 2

How do I combine lists in multiple worksheets

  1. #1
    Desperately seeking shorcuts
    Guest

    How do I combine lists in multiple worksheets

    I have multiple lists on approx 30 worksheets, all in the same workbook.
    They are all in the same format. Is there a way to combine the lists into
    one list with out copying and pasting?

  2. #2
    Ron Coderre
    Guest

    RE: How do I combine lists in multiple worksheets

    You might be able to use MS Query to consolidate Excel ranges from your
    multiple wkshts. This also works for consolidating data from the active
    workbook (Just save it first so Excel can find it):

    This example uses 24 named ranges in different sheets of one workbook.

    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 rngList01, rngList02, ...etc through rngList24

    (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)Data>Import External Data>New Database Query
    >Databases: Excel Files


    Browse to The file, pick ONE of the data ranges 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:\Queries\Lists`.rngList01
    UNION ALL
    SELECT * FROM `C:\Queries\Lists`.rngList02
    UNION ALL
    SELECT *FROM `C:\Queries\Lists`.rngList03
    (continue like that until the last range)
    UNION ALL
    SELECT * FROM `C:\Queries\Lists`.rngList24

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

    Click the button to return the data to Excel.

    That will result in a continuous list of all of the records in every listed
    range.

    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.)


    Is that an approach you can work with?

    ***********
    Regards,
    Ron

    XL2002, WinXP-Pro


    "Desperately seeking shorcuts" wrote:

    > I have multiple lists on approx 30 worksheets, all in the same workbook.
    > They are all in the same format. Is there a way to combine the lists into
    > one list with out copying and pasting?


+ 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