+ Reply to Thread
Results 1 to 2 of 2

Consolidating worksheets

  1. #1
    Joeflo
    Guest

    Consolidating worksheets

    I have 26 worksheets to consolidate. I want to be able to have all worksheet
    names on the consolidation sheet. I also want certain information off each
    of these sheets to be entered on the consolidation sheet. I have tried the
    help, and can get the information from one sheet to the YTD consolidation
    sheet, but, when I try to copy the cell, I get the same info there as well.
    I don't want to have to enter all of these manually, and there must be a way
    that excel does it.

    Any help would be appreciated.

    Thanks

    Joe


  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Without using VB code, and assuming that the total number of lines in all worksheets does not exceed 65536, that you do not have nor want duplicates, and that your names are in column A.

    Copy the names column from each sheet to a spare worksheet to form a long column.
    If you have a main sheet that you want data from other sheets added to copy it's names to columns A AND B.
    If you are starting a brand new consolidation sheet then ignore column B.

    Sort over column A then over column B

    In C1 put
    =IF(B1<>"","",A1)

    In C2 put
    =IF(OR(B2<>"",A2=A1),"",A2)
    and formula copy this to the end of your data.

    Select column C and Copy, then Paste Special = Values back over itsself.
    Delete columns A and B

    You now have either a complete list of names or a list of names to be added to your main sheet, in which case select and Copy these names to the end of your main sheet data.

    For each column of data required from other sheets, in row 1 of that column put a lookup something like:

    =IF(ISERROR(VLOOKUP(A1,Sheet2!A:F,6,FALSE)),"",VLOOKUP(A1,Sheet2!A:F,6,FALSE))

    to pickup data from sheet 2 column F etc, or use:

    =IF(ISERROR(VLOOKUP(A1,[MyOtherBook.xls]Sheet1!A:G,7,FALSE)),"",VLOOKUP(A1,[MyOtherBook.xls]Sheet1!A:G,7,FALSE))

    Select each cell that you put the lookup into and bulk formula drag to the bottom of your data.

    When complete, select these lookup columns and Copy, then Paste Special = Values back over themselves.

    Hope this helps.

    --

    Quote Originally Posted by Joeflo
    I have 26 worksheets to consolidate. I want to be able to have all worksheet
    names on the consolidation sheet. I also want certain information off each
    of these sheets to be entered on the consolidation sheet. I have tried the
    help, and can get the information from one sheet to the YTD consolidation
    sheet, but, when I try to copy the cell, I get the same info there as well.
    I don't want to have to enter all of these manually, and there must be a way
    that excel does it.

    Any help would be appreciated.

    Thanks

    Joe

+ 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