# Thread: Best Practices - Location of Formulas

1. ## 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. 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.

3. ## 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. 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
•