the MOST

Yesterday I received a phone call from a staff member who works with the kind of data that makes my head hurt.  She had a workbook in Excel with about 50(!) worksheets.  The last one was a summary worksheet in which she wanted to total the same cell on every other worksheet.  She asked if there was a better way than manually entering each cell in the formula.  I wasn’t sure it was possible, but she said that she had found mention of it on a message board.  Working together, we found the solution. 

It is =Sum(‘name of first worksheet:name of last worksheet’!cell reference).  The ‘ are only necessary if you have spaces in the names of your worksheets. Your worksheets need to be grouped together.

Let’s look at an example (on a smaller scale).

In this case, we have a worksheet for each quarter that shows employees and their sales figures.  In cell B1 of each quarter’s worksheet, we see the total sales for that quarter.  Notice that the quarter worksheets are named Q1 Sales, Q2 Sales, Q3 Sales, and Q4 sales, and that they are grouped together.

screen capture that shows worksheet names

We have a Total Sales worksheet where we want to sum the total sales for the other four worksheets.

screen capture of totals worksheet

We’ll enter the formula in cell A2.  If you choose to click on the worksheet names to enter them, this will require a some editing. Otherwise, you can simply type the formula in (which I recommend).  Use the apostrophe for the ‘ mark.

screen capture of the final formula

This will work for some other mathematical formulas, like PRODUCT or AVERAGE.
Examples:
=Average(‘Sheet 1:Sheet 5’!A1)
=PRODUCT(‘Sheet 1:Sheet 5’!A1)

I found an interesting tip involving this on www.officearticles.com. If you are constantly adding worksheets to your workbook that should be included in the formula, but you don’t want to keep editing the formula, you can enter a blank worksheet before the first worksheet in the group and name it First. After the last worksheet in the group, insert a blank worksheet named Last. Then you can use the formula:

=SUM(First:Last!A1)

The blank worksheets will not affect the result of your formula because there is not any content. When you add new worksheets, just be sure to place them somewhere between First and Last.

Have you tried this formula? Let me know in the Comments section.