When you need to generate a total from data across multiple Excel worksheets, create a named range you can use in your SUM function.
You have five sheets in your workbook. The first four sheets list the sales for each quarter by month and then total the sales for the quarter in cell N10. The last sheet calculates the year-to-date sales. To have your worksheet summarize the YTD total automatically, follow these steps:
- Go to Insert | Name | Define. (In Excel 2007, click the Formulas tab, click the Define Name arrow, and then click Define Name.)
- Click in the Names In Workbook text box and type Total_Sales.
- Click in the Refers To box.
- Click cell N10 in the 1st Quarter Sales sheet.
- Click the 1st Quarter Sales sheet tab.
- Press and hold Shift while clicking the 4th Quarter sheet tab.
- Click the Add button and then click OK. (In Excel 2007, just click OK).
Now in your summary sheet, you can use Total_Sales in the Sum function to obtain the total YTD sales.