Figure 1-1
Not knowing the benefits of 3D- Reference would result in countless hours of tedious formula entering.
Function:
3D-Reference is an excel function which allows the user to sum up all the worksheets with the same format. For example, in figure 1-1,in cell B4, the cell is the sum of cells I24 of worksheet week 1 to week 5. The purpose of this function is to save the time and effort in entering a formula for each worksheet. Entering a formula once would work for all other worksheets.
How to:
Make a function that applies to all the worksheets
1. Ensure that all the worksheets are the same format. If the total figure for one worksheet is in a particular cell ensure that it is the same for all other worksheets.
2. Place all the worksheets wanted for 3D reference side by side.
3. Hold shift and click on the worksheet farthest to the left and while holding shift, click on the 3D-reference worksheet farthest to the right. This step is to group all the different worksheets and make them "act as one". After this step is complete, whatever function, input, or anything applied to one particular cell in a worksheet will apply to all the worksheets grouped. Note figure 1-2.
Figure 1-2
How to sum up all the worksheets in a non-grouped worksheet
1. On a different worksheet choose a cell in which you want the sum of all the grouped worksheets.
2. On the chosen cell, apply the sum formula. But instead of from cells on the same page, you will have to use cells on the grouped page. With the sum function active click on the a cell in leftmost worksheet, hold shift, and click on the rightmost worksheet. On the formula box you should see that the leftmost worksheet, a colon in between, and the rightmost worksheet. This should be followed by the particular cell selected.
Figure 1-3
Further Reference:
http://www.bettersolutions.com/excel/EED883/QN620422111.htm
http://www.expresscomputeronline.com/20021216/techspace1.shtml
http://www.igetit.net/newsletters/Y05_08/FormulasIn3D.aspx
http://www.umbc.edu/oit/sans/helpdesk/Microsoft/Excel/HOWTO_Create_Links_Between_Worksheets.html
