Excel Tip: Manipulating 3-D Worksheets
For those of you who have an interest in working with multiple spreadsheets within a single Excel file, here's a quick tip for manipulating numbers in a situation where you have several sheets of identical format, but with different numbers.
Say you have 12 active sheets, with sheet 1 as the summary sheet and sheets 2-11 providing the detail, and you want to add the contents of Cell D5 from sheets 2-11 and place the sum in cell D5 of sheet 1.
- Step 1: Place your cellpointer in cell D5 of sheet 1 and initiate the formula by clicking the AutoSum button on the standard toolbar. At this point the formula will appear as: =SUM()
- Step 2: Click on the tab for sheet 2, then click once in cell D5 of sheet 2. At this point your formula will read: =SUM(Sheet2!D5)
- Step 3: Hold down the Shift key, then click the tab for sheet 11. At this point the formula will read: =SUM(Sheet2:Sheet11!D5)
- Step 4: Press Enter (or click AutoSum again) to finalize the formula.