Excel cell reference: Working with cell references to another worksheet

YOUR OBJECTIVE

You listed your company’s first-quarter sales from varions business locations in several tables. To obtain a clearer overview, you recorded the sales for each month on a separate worksheet. That is, there is a worksheet for January, another for February, and a third for March.

Now, on a new worksheet called Berlin, you would like to compile the sales figures just for Berlin’s first quarter. This table should always contain the current sales numbers from the original tables. To accomplish this you will have to use ((fett))excel cell references((fettende)) to link the contents of the new table’s cells to the contents of existing cells in the other worksheets.

In this learning module you will be shown how to create and use excel cell references when the cells being referenced are located on other worksheets .

HOW DOES EXCEL CELL REFERENCES  WORK?

In principle, constructing  excel cell references that link to cells located in other worksheets (in the same Excel workbook) works in a fashion very similar to building excel cell references to cells within the same worksheet: First you must activate the cell in which you wish to construct the excel cell reference, and then click on the relevant tab to switch to the worksheet where the cell to be referenced is located. There you should click on the cell to which you want to link. If you then press [Enter], the reference is completed and Excel brings you back to the worksheet in which you began constructing the excel cell reference. Here is a concrete example:

You want to create a summary table for product sales for the first quarter in Berlin. To create a cell reference that links to the first value for chocolate ice cream sales in January, begin in the Berlin worksheet. There, click cell B4 and enter an equal sign (=). To create a reference to the source cell in the January worksheet, go to the January worksheet by clicking once on its tab (bottom, left). Now, on the January worksheet, click the cell to which you want to link, in this case cell B4.

If you now press [Enter], the cell reference you are building will be complete.

HINT

While constructing the cell reference , you can observe it in the formula bar. The complete cell reference, in this case will be displayed there. The initial part of the entry, the part ending with an exclamation mark (January!), identifies the worksheet where the source-data cell is located.

HINT

Instead of manually entering the cell references for the other ice cream flavors into the January column, you can use the AutoFill function. To do this, position your mouse pointer over the bottom-right corner of cell B4. It will change to a small black cross . Now, while holding the left mouse button depressed, pull the cross down until you’ve covered cell B7. The intervening cell references will have been filled in, and the values for the other ice cream flavors as well as the total value are now displayed. In order to retain any formatting that may already have been applied to the cells you just filled in, you can click the SmartTag that appears below the filled-in range of cells. From the short selection list that appears, activate the Fill Without Formatting option:

Now you want to build similar cell references for the February and March data in the unfilled columns: Begin by clicking on cell C4 on the Berlin worksheet, then go to the February worksheet where you will click cell B4. When you press [Enter], the cell reference will be complete. Then use AutoFill to fill in the rest of the column. For March create the first cell reference “=March!B4″ in a similar fashion, and again use AutoFill to complete the column.

The table on the Berlin worksheet will now always display the current sales data for the first quarter.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen