Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multisheet cell capture (Excel 2002)

    I have a multi-sheet workbook (now 10) that may grow upwards of 150 sheets (all identical but with differing sales data), I will need to capture data from various parts of the individual sheets and create a one-line summary on a summary sheet. Essentially I will have a 150 line summary report (representing each worksheet) from which I can perform additional analysis. I would want to be able to create a list of the sheet numbers(each sheet will be named with a 4 digit number) in a column in the "Summary Report" and based on that list somehow use it as the key to look into that particular sheet and return the appropriate data from 9 specific cells( i.e. f8,b4, e9,etc.). Thanks Woody for a great place for real world answers.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multisheet cell capture (Excel 2002)

    The approach I use follows.

    In the attached sheet is some code including a simple userform to list the sheet names, which I have in my Personal.xls. Import the code and userform modules to either your Personal.xls (probably the best place) or the target sheet. Attach the macro to a button if you wish, I usually just grab it through Alt-F8.

    Run the macro to list all sheet names (which will include the sheet you list all sheet names on, which may be unnecessary, so just delete any sheetnames you don't want).

    Then use =INDEX() to derive the sheet references you want for cell E8 in each sheet; if the sheet names are in a column starting at cell A2, the formula in B2 would be:

    =INDIRECT(A4&"!E8")

    if there are spaces in your sheetnames, it'll have to be:

    =INDIRECT("'"&A4&"'!E8")
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multisheet cell capture (Excel 2002)

    Have you looked at the Data | Consolidate menu? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multisheet cell capture (Excel 2002)

    John:
    This post was requested several months ago and I did use your suggestion, I am sorry for not replying sooner, thanks so much for your help and for this forum.

Posting Permissions

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