Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2011
    Location
    Austin
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best Practices - Location of Formulas

    Hi All:

    I have 12 monthly workbooks and one summary workbook. Where would you place the formulas that create the data of the summary:

    1) In each individual workbook with links from the summary page?

    or

    2) Put all of the formulas in the summary since that is where they are used?

    Thanks for your help,

    baribill

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    BariBill,

    Welcome to the Lounge! Hope you enjoy your stay.

    What I'd do is create one workbook with a worksheet for each month and one for the Yearly Summary. You can then use a 3D {the current fad isn't it} formula to summarize all the months i.e. On the summary page for Net Income if the value for each month is in J42 on each sheet (it should be in the same place on all sheets) you'd enter =Sum(Jan-Dec!J42). Of course you'd do the same for all the cells.

    Note: Replace the - in the formula above with a colon. The board's software interprets a colon as a Smiley!

    This setup also allows you to do group editing and formatting on all sheets at once.
    Last edited by RetiredGeek; 2011-02-02 at 17:02. Reason: Board software interprets colon in formula as icon!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Feb 2011
    Location
    Austin
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separation of Worksheets

    Hi Retired Geek:

    Way back in the beginning (eight years or so) that is what I did. However, as we progressed through the year and the worksheet got bigger and bigger, I was beginning to see unacceptable "Save" times. So, the next year was when I went to the concept of one worksheet/month. So when I am through with January, I don't have to save it anymore.

    I finally put the formulas into the summary worksheet and have not seen any degradation in terms of speed. But I tell you, they are some really ugly formulas with all of the worksheet information that I now have to carry.

    Baribill

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you are truly looking for a "best practice" I would eliminate the monthly sheets altogether. It is not good practice to ahve multiple identically formatted sheets especially if you want to summarize the data.

    It is better to append each month to the next. Include a column for date (or one for the 1st of the month and year, formatted to display month and year). this allows using a pivot to summarize the data, filtering to get the monthly display and the ability to chart all, but month, or with any other filter you use.

    Steve

Posting Permissions

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