Create a link to or Sum a cell in all worksheets (Worksheet Functions without VBA)
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
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
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
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
Note : I use the worksheet function Sum in this example but you can also use other functions.
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)