tag:blogger.com,1999:blog-65607643136716877872024-02-18T23:33:42.961-08:00Using 3D Cell Referencesin Microsoft Excel 2007BSYS 2050 Projecthttp://www.blogger.com/profile/15212816490703886629noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-6560764313671687787.post-80220744580629508672008-02-23T22:17:00.001-08:002008-02-28T17:49:07.457-08:003D References: Explanation and Examples<span xmlns=""><strong>What are 3D References?<br /></strong><br />3D References are <a href="http://www.expresscomputeronline.com/20021216/techspace1.shtml">cell references</a> (sets of coordinates) that point to another cell or range of cells in a different worksheet (or even workbook).<span style=";font-family:Trebuchet MS;font-size:10;" ><span style="color:blue;"><br /></span>Where a cell reference might be B3 (Column B, Row 3 in <strong>this worksheet</strong>) a 3D reference would be 'Sheet 2'!B3 (cell B3 in <strong>Sheet 2</strong>).</span> They can also refer to the same cell or range on <span style="text-decoration: underline;">multiple</span> worksheets.<span style="color:red;"><br /></span>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.<br /><br /><strong>When will we use a 3D Reference?<span style="color:red;"><br /><br /></span></strong></span><span xmlns=""><div><div><div><p>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…<br /><br />='Quarter 1'! B6 + 'Quarter 2'! B6 +'Quarter 3'! B6 + 'Quarter 4'! B6 </p><p><em>(Note: In this example, "B6" refers to the cell that contains the quarterly subtotal) </em><br /></p><br /><p><strong>A Specific application of 3D Referencing </strong><br /></p><p>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. </p><p><a href="http://www.mediamax.com/karen_qian/Hosted/Original%20Version.jpg"><img id="BLOGGER_PHOTO_ID_5170455560379579234" style="margin: 0px auto 10px; display: block; text-align: center;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA6aiqhXC8ED6LhoBXFWg3V14rNfkrZJ0pWT_DHh9RQzbVWWxud2BJLtK4Z2dtlp2gLBcLnWZDxylvz0oFkLUlHOQ2rNbmH9m7Pds3lsfjJnIs14UxnLsaf2x-f0HBz8PVum6zUrO8T4Y/s400/Original+Version.jpg" border="0" /></a> <strong><br /></strong></p><p><strong>Using 3D referencing to add data from multiple worksheets<br /></strong><br />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:<br /></p><br /><p><a href="http://www.mediamax.com/karen_qian/Hosted/Employee%20sheet.jpg"><img id="BLOGGER_PHOTO_ID_5170454821645204306" style="margin: 0px auto 10px; display: block; width: 385px; height: 207px; text-align: center;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3ZkGHKfbGgoiN8kyiGRjPUVs4MVOYNm-VNMSM-R9p-SHkfQQ31Sad0_-2EnY5C1zhwDgo1bfNulFmXYb_WMCWo6HTozimVcSC4UX0fufbEwpNeYTn-ALhboPb19CdM_HiEHQH-TLeCHw/s400/Employee+sheet.jpg" border="0" height="181" width="400" /></a> 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.<span style="color:red;"><br /></span>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.<br /><br />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 <span style="text-decoration: underline;">and</span> PST, or HST. The summary sheet then references the subtotals and uses the information to calculate totals for each tax account. </p><p>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. </p><a href="http://www.mediamax.com/karen_qian/Hosted/Summary%20Table.jpg"><img id="BLOGGER_PHOTO_ID_5170456449437809522" style="margin: 0px auto 10px; display: block; text-align: center;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVqYbyKiwEYUuxJU83RWG2HqQebGBIjE1t_G_s41m3UWFtgTRkI_o3FEhbHNH1ZZR7WzUGkQCn4PU-1SxJ-nzF7GfFko3QfRwI_9JQM3I-YLxTzvw25ctmXiZo-n31aSEp3N9kpWN2ROQ/s400/Summary+Table.jpg" border="0" /></a> <strong><br />Using 3D References in <a href="http://www.homeandlearn.co.uk/ME/mes10p2.html">data validation </a>to create drop-down menus </strong><br /><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE9qWwVYPJcSUcOO-IeXpOWd6r5Z9jhxDXCyaSkTfEEYx2ZdizGcGVuX24cTa_BrWfyyst4dxD5Tu159pxtyWhGmhnYYFaVxnLPbZEb5wpwd5QuN8-MZRWXOObeO6j6R3FBPkI4kEg2Og/s1600-h/Drop+Down.jpg"><img id="BLOGGER_PHOTO_ID_5170489920117947474" style="margin: 0px 10px 10px 0px; float: left; width: 259px; height: 162px;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE9qWwVYPJcSUcOO-IeXpOWd6r5Z9jhxDXCyaSkTfEEYx2ZdizGcGVuX24cTa_BrWfyyst4dxD5Tu159pxtyWhGmhnYYFaVxnLPbZEb5wpwd5QuN8-MZRWXOObeO6j6R3FBPkI4kEg2Og/s320/Drop+Down.jpg" border="0" height="209" width="320" /></a>In order to input information in the table, some fields only allow a drop down menu. An example of this is the description menu. </p></div></div></div><p>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. </p><p>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<span style="color:red;"> </span>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.</p><p><br /></p><p><strong>An example involving multiple references</strong></p><p><strong></strong>Cell B6 could also be an absolute reference like $B$6. We have omitted the $ for our examples in the interest of readability.</p><p>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? </p><a href="http://www.mediamax.com/karen_qian/Hosted/Payments%20Log.jpg"><img id="BLOGGER_PHOTO_ID_5170456608351599490" style="margin: 0px auto 10px; display: block; width: 424px; height: 254px; text-align: center;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih2vlNPhW4-5F4okwovGsqEVyphPwMRfxPqYE7MJowa1pLAY_MLpLU9cq8nxLkC2HHNObPqsAZGcjp0GltcJJX0p59yLMjtMhFYmsqWyipZVLXa6BGNlSxCrLx6j_GipHFSJC5GkKkBtQ/s400/Payments+Log.jpg" border="0" height="224" width="418" /></a> Our solution:<br /><p>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)</p><p>2. Create a list of employee names in the Reference Values worksheet<br /></p><img id="BLOGGER_PHOTO_ID_5170474557019929602" style="margin: 0px auto 10px; display: block; text-align: center;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0UlLvcAlAlg07FzKSujtDP4c4ouX96RDABcJYSAYJyukL4FlNmIxo3zD26ZOoeQXM3ML1R96G1y_4ixnvQbxae5IhINlF-Fxb-uKkqE2_ehvU7PloDINi4RS5MgqSPN0zFkhqyV4eHtA/s400/Picture6.jpg" border="0" /> 3. Name this list "Employee_Names"<br /><br /><p>4. Use Data Validation to create the drop down menu </p><a href="http://www.mediamax.com/karen_qian/Hosted/Data%20Validation.jpg"><img id="BLOGGER_PHOTO_ID_5170459498864589730" style="margin: 0px auto 10px; display: block; width: 400px; height: 308px; text-align: center;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYhDwFNVlabt-DSCNwRlCHeHS-4bd_VUK9WoJwQ7PSoCnvB1byeUAQOIDJh-LsGtaYsxgLhAO1fsQXNzmT9hdQN1DIx9TN7_w-oxFzFdmEF_mo5FSFkkajIIo08T2j9TKbDPb5rYvixaU/s400/Data+Validation.jpg" border="0" height="314" width="400" /></a><br /></span>BSYS 2050 Projecthttp://www.blogger.com/profile/15212816490703886629noreply@blogger.com0