K-tips:Excel - Formulas across worksheets

Business Computing Tips

By K&K Fainges kfainges@...

One of the most common uses for excel is to add up accounts for the
year. An easy way to do this is to put each month on a different
worksheet (or page). Then have one page at the end that adds
everything up.

The easiest way to do this is to fill in the page for January first.
Format it just the way you want it and total everything for that
month. Then the magic of excel takes over. Right click on the tab at
the bottom of that page. It will probably read sheet1 unless you have
changed it.

Click on move or copy. Click on sheet2 so it adds this page just
after sheet1, and click in the small box to put a tick in 'create a
copy'. A sheet named Sheet1(2) will appear. Click on it and it will
have all the same formulas everything.

Repeat until you have all the sheets for January to December. You can
rename them by right clicking on the tab and clicking on rename if
you like.

Then click on sheet2. You can rename it totals if you like. Now to
add up all the worksheets. Click in the cell where you want the
answers. Then click on the sum function on your toolbar, or go to
INSERT FORMULA SUM. Click on the figure on sheet1 (or January) that
you want totalled for the year. Then hold down the shift key and
click on the tab for December's figures. Then hit enter. It will give
you a formula like =SUM(Jan:Dec!A8) When you put figures in, viola,
totals for the entire year with just a few clicks.

Although you wouldn't add another sheet in this example, you can also
add worksheets in between those two pages and they will be
automatically be added into the formula. Be careful that you want
everything on those pages included or you can get some very strange
totals.

If you want to skip some pages, click on each page separately, but
you will need to click on the actual cells to get the formula to
work.


**********************************************************************

Please feel free to pass it on to your friends, just let them know I
wrote it.

Karen Fainges holds a Bachelor of Business, and a Grad. Cert of
Vocational and Educational Training. All this is nice but it's the 14
years of having to make sales or starve that makes her think she has
really learnt what does and doesn't work. A tutor for all ages, she
specialises in helping people get started on the long road to
technology.

"It has to be practical, it has to be cheap, and it has to work."