+ Reply to Thread
Results 1 to 6 of 6

How do I collate data from 36 sheets into a summary sheet?

  1. #1
    Registered User
    Join Date
    04-29-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    How do I collate data from 36 sheets into a summary sheet?

    Hi, could someone help me with a problem?

    I need to pick 2 cells from 1 worksheet (ditto for 36 worksheets), and collate the info onto a Summary sheet (see attached sample).

    Per the sample, I'm trying to pick D26 and D31 from each of the 36 sheets. This will be placed in a 2 column table in the Summary sheet.

    I'd need to do this every month, and it may be a different item the next month...(eg. could be D24 next month).

    Is there a simpler way than copy-and-paste? A colleague told me to use macros, but I'm code-illiterate. I've tried to search the web for something similar, but due to my limited understanding, I'm unable to modify very much what I've found. For eg. I found this --> http://www.rondebruin.nl/copy2.htm.

    Can someone help me with a simpler macro? I'm capable enough to change the cell, if it's not too complex. Thank you very much in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: How do I collate data from 36 sheets into a summary sheet?

    You could try this formula in B2 and copy down, then do similar for C2:

    =INDIRECT("'"&A2&"'!D26")

  3. #3
    Registered User
    Join Date
    04-29-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How do I collate data from 36 sheets into a summary sheet?

    BINGO StephenR! Thank YOU!

    May I ask what do those quotes, double quotes, & and ! mean? It'll help me modify this formula for future use...

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: How do I collate data from 36 sheets into a summary sheet?

    With Indirect what we are doing is building up a string which is equivalent to a cell reference, in effect what you would do if you were entering the formula directly. If you were doing the latter you would write

    ='Sheet1'!D26

    so we have to incorporate the single quotes and exclamation mark because that is Excel's syntax.

    =INDIRECT("'"&A2&"'!D26")

  5. #5
    Registered User
    Join Date
    04-29-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How do I collate data from 36 sheets into a summary sheet?

    Thank YOU, again. <Deep bow>

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: How do I collate data from 36 sheets into a summary sheet?

    No problem. Don't get stuck down there.

+ 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