Question

What will be the correct reference if you are linking cells D4:D20 of Sheet 1 to get the sum of the selected range in cell A1 of Sheet 2.            ​

Answer 1

Explanation:

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