### Create a link to or Sum a cell in all worksheets (Worksheet Functions without VBA)

Create a link to a cell in all worksheets

Create a link to a cell in all worksheets

If you only have a few sheets you can do this :

- Enter the = sign in the cell in the Summary sheet where you want to have the link
- Click on the worksheet tab where the cell is where you want to link to
- Click on the cell and press Enter
- The formula looks like this if the sheet name is Sheet1: =Sheet1!A1
- Do the same for your other sheets

**But if there are a lot of worksheets in your workbook then this is a lot of work.**

If your sheets are named like this:

**Sheet1**

Sheet2

Sheet3

Sheet2

Sheet3

Then enter the following formula in a cell in the first row of your Summary sheet.

=INDIRECT("'Sheet" & ROW()&"'!A1")

Copy the formula down,

The formula in row 1 link to cell A1 in the sheet Sheet1

The formula in row 2 link to cell A1 in the sheet Sheet2

The formula in row 3 link to cell A1 in the sheet Sheet3

Or if your sheets are named like this:

**Week 1**

Week 2

Week 3

Week 2

Week 3

Then enter the following formula in a cell in the first row of your Summary sheet.

=INDIRECT("'Week " & ROW()&"'!A1")

Copy the formula down,

The formula in row 1 link to cell A1 in the sheet Week 1

The formula in row 2 link to cell A1 in the sheet Week 2

The formula in row 3 link to cell A1 in the sheet Week 3

Or you can enter the sheet names in column A

**Firstsheetname**

Secondsheetname

Thirdsheetname

Secondsheetname

Thirdsheetname

Then enter the following formula in a cell in the first row of your Summary sheet.

=INDIRECT("'" & A1 & "'!B1")

Copy the formula down,

The formula in row 1 link to cell B1 in the sheet Firstsheetname

The formula in row 2 link to cell B1 in the sheet Secondsheetname

The formula in row 3 link to cell B1 in the sheet Thirdsheetname

Another way is to use a macro to create the links for you

Create a summary worksheet from all worksheets with formulas with VBA macro

#### Sum a cell in all worksheets

**: I use the worksheet function Sum in this example but you can also use other functions.**

Note

Note

This formula sum cell A1 in the sheets named Sheet2 and Sheet5 and in all sheets between the two sheets.

=SUM('Sheet2:Sheet5'!A1)

**Tip:**create two new worksheets--one to the far right and one to the far left. Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets with this formula:

=Sum(Start:End!A1)

Then you can drag sheets in and out of that sandwich to play what if games. I'd put a couple of notes on each of these sheets: "don't delete this sheet!" And protect the worksheets so that people don't use it for real data.

**Tip:**from Lori in the Public.Excel newsgroup on Nov-3-2006

=SUM('*'!A20)

Where the

**'*'**automatically converts to all sheets other than the activate one. If your workbook have 6 sheets and you enter this formula in sheet3 the formula looks like this:

=SUM(Sheet1:Sheet2!A20,Sheet4:Sheet6!A20)