Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    calculating only new sheet appended to a workbook (Excel 2002)

    I need to build a workbook that has a new sheet added each month. The new sheet is created from another application that produces an Excel workbook and the new sheet will be appended to the main workbook. When that's done, I need the summary sheet in the main workbook to calculate a number of things based on this new sheet, while retaining, and preferably not recalculating, calculations done for previous sheets that have been added in prior months. Yes, I could recalc everything but as the workbook grows, the recalc of every sheet is going to take too long for the overall workbook.

    If that's not clear, how about a simplified example? Suppose the January sheet is appended. Now I want to calc info based on January in the first/summary sheet. When the February sheet is appended I want to calc info based on the February info but not have to re-do all of the calcs that created the summary for January.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating only new sheet appended to a workbook (Excel 2002)

    One way which might be pertinent is to 'freeze' the previous month's data by selecting all, copy, and paste special values (and formats, if you like) it on top of itself. This knocks out all formulas, so nothing to calculate, in that sheet.
    Another way, if the number of cells is modestly small, is to copy as picture the range concerned [hold shift key down, then edit | copy], then delete the data and paste the picture on top of where the picture was. Advantages = the picture is 'completely' un-editable, and you might find a significant reduction in file size.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: calculating only new sheet appended to a workbook (Excel 2002)

    I thought about the Copy/Paste Special...maybe even making it a macro. The image idea is something to ponder. Thanks for the ideas.

Posting Permissions

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