To make your formulas and functions work correctly, you need to refer to the cells and ranges you want. This section makes sure you know how to refer to cells and ranges, both when they're on the same worksheet as the formula and when they're on a different worksheet. You can even refer to cells and ranges in another workbook—as long as it will remain in the same place in your Mac's file system.
To refer to a cell on the same worksheet, simply use its column lettering and its row number. For example, use =A10 to refer to cell A10.
To refer to a cell on a different worksheet, enter the worksheet's name followed by an exclamation point and the cell reference. For example, use =Supplies!A10 to refer to cell A10 on the worksheet named Supplies. The easiest way to set up such a reference is like this:
NOTE: Instead of clicking the Enter button on the formula bar in step 4, you can press Return to enter the cell reference. When you do this, Excel moves the active cell to the cell below the formula on the worksheet. You then need to press the up arrow key before taking Step 5.
NOTE: If the worksheet's name contains any spaces, you must put the name inside single quotes; for example, ='Sales Results'!A10 rather than =Sales Results!A10. You can also use the single quotes on worksheet names that don't have spaces if you find it easier to be consistent. If you omit the single quotes when they're needed, Excel displays an error message.
To refer to a cell in a different workbook, enter the workbook's path, then the file name in brackets, then the worksheet's name, and then the cell reference. For example, the reference ='Shared:Spreads:[Results.xlsx]Sales!'AB12 refers to cell AB12 on the worksheet named Sales in the workbook Results.xlsx
in the Shared:Spreads folder.
Unless you happen to know the path, file name, worksheet name, and cell, it's usually easiest to set up the reference using the mouse. Follow these steps:
When you create a reference to a cell in another workbook, Excel keeps the value of the referring cell updated. If you move or delete the other workbook, Excel displays a dialog box warning you that it can't find the workbook and asking if you want to locate it manually.
To refer to a range that consists of a block of cells, give the addresses of the first cell and the last cell, separating them with a colon. For example, to refer to the range from cell P10 to cell Q12, use =P10:Q12.
To refer to a range that consists of individual cells, give the address of each cell, separating the addresses with commas. For example, to refer to cell J14, cell K18, and cell Z20, use =J14,K18,Z20.
To refer to a range on a different worksheet or in a different workbook, use the techniques explained in the previous section. For example, if you need to refer to the range P10 to Q12 on the worksheet named Stock Listing, use ='Stock Listing'!P10:Q12.
Sometimes you may need to make the contents of one row or column refer to another row or column. For example, say you need to make each cell in row 25 refer to the corresponding cell in row 4, so that cell A25 refers to cell A4, cell B25 to cell B4, and so on.
To do this, click the row heading for row 25, selecting the row. Then type =4:4 to create the reference to row 4, and press Cmd+Return to enter it in all the cells of the selection. Similarly, you can refer to a whole column by entering its letter designation, a colon, and the letter designation again; for example, E:E.
UNDERSTANDING ABSOLUTE REFERENCES, RELATIVE REFERENCES, AND MIXED REFERENCES
To make your references easier to enter and recognize, you can assign a name to any cell or range, as discussed in the section “Identifying Parts with Named Ranges” in Chapter 3. You can then refer to the cell or range by the name, even if it's on another worksheet whose name you've forgotten.
18.225.72.245