Creating a 3-D reference to the same range of cells on multiple worksheets


The following tutorial will describe how to create a 3-D reference to the same cell range on multiple worksheets. A reference that refers to a same cell or a range of cells on multiple worksheets is known as 3-D reference.


The 3-D reference is a convenient and a useful way to refer several worksheets that same pattern and type of data, such as when you want to find the sum of the first 6 odd numbers, 6 even numbers and 6 squares. In a similar way, you can use the 3-D reference to consolidate the budget of various departments such as Marketing, Finance and HR in your organization.

What happens to 3-D reference when you insert, delete, copy and move worksheets

The following section describes what happens when you insert, delete, copy or move worksheets that are included in the formula that contains 3-D reference with an example. The example that we are using is =SUM (sheet1:sheet4!, A1:A6). The mentioned formula sums the values in cells A1 through A6 in worksheets sheet1 through sheet6.


When you insert or copy a worksheet

If you copy or insert worksheets between sheet1 and sheet 4 (endpoints in our example), the excel application includes all values in cells A1 through A6 from the inserted worksheets to calculations.


When you delete a worksheet

If you delete worksheets between sheet1 and sheet4, the excel application removes the values contained in those worksheets from the calculation.


When you move worksheets

If you move worksheets between sheet1 and sheet 4 to a different location that is not in the vicinity of the referenced range, the excel application remove their values from the calculations.


When you delete an endpoint

If you move any of the worksheets sheet1 or sheet4, the excel removes values from that workbook during the calculation.


When you move an endpoint

If you wish to move either of the endpoints sheet1 or sheet4 to a different location in the same workbook, the application adjusts the calculation in such a way that the new worksheets are included between them unless you reverse the endpoints. If you reverse either of the endpoints, the 3-D reference changes the endpoint automatically. For instance, if you move the worksheet sheet1 after sheet6, the endpoints of the formula becomes sheet2:sheet4!, similarly, if you move sheet4 after sheet1, the endpoints of the formula becomes sheet1:sheet3!.


Steps to create a 3-D reference

These are the four worksheets that we are using in this example.

A worksheet that contains the first 6 odd numbers

A worksheet that contains the first 6 square numbers

A worksheet that contains the first 6 even numbers

A worksheet that contains the first six prime numbers

The function we are using is the SUM function that calculates the sum of all the first six odd numbers, squares, even numbers and prime numbers.

=SUM (oddnumsrimenums!A1:A6)

1) Click on any cell in the worksheet where you wish to enter the function.

2) Enter an Equal to (=) sign in that cell, followed by the function name that you want use and then followed by an open parentheses.

3) You can use any of the built-in functions provide by the Excel application that suits your requirement.

4) Now, click on the worksheet tab that you want to add as the first worksheet in the function.

the worksheet referred here is the oddnums worksheet


5) Then, holding down the SHIFT key, click on the worksheet tab that you want to add as the last worksheet in the function.

the worksheet referred here is primenums worksheet


6) Next, select the range of cells that you want to include as a reference.

the range referred here is A1:A6

7) Complete the formula by entering closing parentheses and then press ENTER key.


8) The result of the function will be obtained in the cell where you entered the formula.


So, this is how you can create a 3-D reference to refer to several worksheets in a workbook that contain similar type and pattern of data in Microsoft Excel 2010 application.


Read Other Applications