Learn IT

Question of the week of 6/13/11 – Excel summary sheet linking to other worksheet

Question: I have an Excel Workbook with several worksheets. On the summary sheet I want to refer to the values on various individual worksheets. How can I do that?

Answer: The easiest way is just to click in the Summary worksheet cell where you want the cell’s value and press = (to indicate a formula) and then click on the source worksheet and cell you’re referring to and press enter (or the formula check box). This creates an automatic link to the contents of that particular cell. If the source worksheet is modified by adding or deleting rows or columns, the summary sheet link will change based on the new cell address (relative cell address). Absolute cell addresses ($B$12) would assure that that exact cell  address B12 is always used regardless of modifications to a worksheet.

I would also recommend using Excel Pivot Tables to simply your work even more, and providing some powerful tools. However, your data needs to be set up with contiguous rows and columns, eliminating blank, total and subtotal rows and columns. Pivot Tables can be a tremendous timesaver for budgets and other worksheets with a lot of rows and columns and allows you to create a variety of reports and charts by updating the original set of data. There are several online tutorials at http://office.microsoft.com/training (and select Excel) which can get you started. ITS also teaches sessions occasionally on Pivot Tables.

Leave a Reply