 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
If you only have a few sheets you can do this:
1)
Enter the = sign in the cell in the Summary sheet where you want to have the
link
2) Click on the worksheet tab where the cell is
where you want to link to
3) Click on the cell and press
Enter
4) The formula looks like this if the sheet name
is Sheet1: =Sheet1!A1
5) 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 (formulas with VBA)
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:
from Dave Peterson
I'd 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. WARNING: Not working in Excel 2002, it
crashes the application. 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)