3D References are cell references (sets of coordinates) that point to another cell or range of cells in a different worksheet (or even workbook).
Where a cell reference might be B3 (Column B, Row 3 in this worksheet) a 3D reference would be 'Sheet 2'!B3 (cell B3 in Sheet 2). They can also refer to the same cell or range on multiple worksheets.
You can use them to add values across worksheets with ease. They are a useful and convenient way to reference several worksheets that follow the same pattern and contain the same type of data.
When will we use a 3D Reference?
An example of using 3D references would be a sales workbook, with separate worksheets for each quarter. You could create a summary sheet to show a comparison of quarterly amounts, and to give an annual sales total. Your 3D reference for total annual sales might look something like this…
='Quarter 1'! B6 + 'Quarter 2'! B6 +'Quarter 3'! B6 + 'Quarter 4'! B6
(Note: In this example, "B6" refers to the cell that contains the quarterly subtotal)
A Specific application of 3D Referencing
We were given a single page worksheet that tracked general employee expenses and did not calculate taxes correctly. We took this existing spreadsheet and made improvements to it in order to increase its functionality, and correct the tax error. We decided to create a separate worksheet for each employee, and a summary sheet. This way, the company can keep detailed records of each expense, which employee generated it, and what it was specifically used for. 3D References were used to summarize data, update the employee list, and create dropdown menus.
Using 3D referencing to add data from multiple worksheets
For this workbook, there is a separate version of the expense worksheet that the employee will complete, allowing accounting staff to copy and paste into the master workbook. This is what the employee worksheet looks like:
In order to be reimbursed by the company for their expenses, each employee must fill out this sheet electronically. In the top left corner (cell B 13), the sheet automatically calculates the total expenses, as well as the total owed to the employee.
Using a 3D reference, cell B13 from every employee expense worksheet will be totaled on the Payments and Summary worksheet. The total will automatically update with new entries. On the payments sheet, the user will quickly be able to fill in any amount paid and it will automatically change the amount unpaid, thus keeping a current balance at all times.
In the tax code column, the tax breakdown has been pre-calculated, according to which code the user selects. They have a choice of GST, PST, GST and PST, or HST. The summary sheet then references the subtotals and uses the information to calculate totals for each tax account.
The summary sheet that tracks all the totaled balances looks like this. This sheet shows the user the total amount of expenses the company's employees have incurred.
Using 3D References in data validation to create drop-down menus
In order to input information in the table, some fields only allow a drop down menu. An example of this is the description menu.
The only options are meals, client entertainment, lodging, transportation, parking, and other. If you choose other, you will have to give a description in the explanation column.
In order to create all the 3D reference drop boxes for each of the employee logs, we used a separate worksheet titled Reference Values. We have created named ranges which contain the values of several variables (Tax_Codes, Tax_Rates, Description, Employee_Names). We then used a List Data Validation with the range =Named_Range to create the drop boxes. If you wanted to add "Telephone Expense" because the company decided to supply each employee with a private cell phone, you could simply add the new expense account to the Reference Values sheet to have it appear as an option on the drop down tab.
An example involving multiple references
Cell B6 could also be an absolute reference like $B$6. We have omitted the $ for our examples in the interest of readability.
Here is one of the challenges we had. How could we create a drop down menu for Employee names that will update when a new employee is added?
Our solution:1. Enter an employee name in cell B12 of an employee worksheet (this is the only place a user needs to enter the employee's name)
2. Create a list of employee names in the Reference Values worksheet
3. Name this list "Employee_Names"4. Use Data Validation to create the drop down menu
