Which references in Excel helps us to refer to cells in other worksheets within the same workbook?
|
by Karyn Stille Show
Excel uses two types of cell references to create formulas. Each has its own purpose. Read on to determine which type of cell reference to use for your formula. Relative Cell References This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using AutoFill. Example: =SUM(B5:B8), as shown below, changes to =SUM(C5:C8) when copied across to the next cell.  Absolute Cell References Situations arise in which the cell reference must remain the same when copied or when using AutoFill. Dollar signs are used to hold a column and/or row reference constant. Example: In the example below, when calculating commissions for sales staff, you would not want cell B10 to change when copying the formula down. You want both the column and the row to remain the same to refer to that exact cell. By using $B$10 in the formula, neither changes when copied. A more complicated example: Let's pretend that you need to calculate the prices of items in stock with two different price discounts. Take a look at the worksheet below. Examine the formula in cell E4. By making the first cell reference $C4, you keep the column from changing when copied across, but allow the row to change when copying down to accommodate the prices of the different items going down. By making the last cell reference A$12, you keep the row number from changing when copied down, but allow the column to change and reflect discount B when copied across. Confused? Check out the graphics below and the cell results. Copied Across Copied Down Now, you might be thinking, why not just use 10% and 15% in the actual formulas? Wouldn't that be easier? Yes, if you are sure the discount percentages will never change - which is highly unlikely. It's more likely that eventually those percentages will need to be adjusted. By referencing the cells containing 10% and 15% and not the actual numbers, when the percentage changes all you need to do is change the percentage one time in cell A12 and/or B12 instead of rebuilding all of your formulas. Excel would automatically update the discount prices to reflect your discount percentage change. Summary of absolute cell reference uses:
There is a shortcut for placing absolute cell references in your formulas! When you are typing your formula, after you type a cell reference - press the F4 key. Excel automatically makes the cell reference absolute! By continuing to press F4, Excel will cycle through all of the absolute reference possibilities. For example, in the first absolute cell reference formula in this tutorial, =B4*$B$10, I could have typed, =B4*B10, then pressed the F4 key to change B10 to $B$10. Continuing to press F4 would have resulted in B$10, then $B10, and finally B10. Pressing F4 changes only the cell reference directly to the left of your insertion point. I hope this tutorial has made these cell reference types "absolutely" clear! Microsoft Office
Tutorials A reference is a cell's address. It identifies a cell or range of cells by referring to the column letter and row number of the cell(s). For example, A1 refers to the cell at the intersection of column A and row 1. The reference tells Formula One for Java to use the contents of the referenced cell(s) in the formula. If a formula requires a number, you can substitute a reference to a cell that contains a number. You specify a range of cells by placing a colon (:) between two cell references. For example, A1:C3 refers to the range anchored by cells A1 and C3. The range includes all cells in columns A, B, and C of rows 1, 2, and 3.
Entering Cell ReferencesYou can enter cell references in formulas in three ways:
Absolute and Relative ReferencesThere are two types of cell references: relative and absolute.
References that are part absolute and part relative are called mixed references. The following table lists the reference types.
Using Relative and Absolute ReferencesYou can copy and paste absolute, relative, and mixed references to create worksheets that are easy to update and that are smaller than worksheets where each formula is created separately. For example, in the following worksheet, the values in column A need to be multiplied by the percentages in row 1.
To do these calculations, you could enter the formula =A2*B1 in cell B2, =A3*B1 in cell B3, =A4*B1 in cell B4, etc. Besides the fact that this would require a lot of typing, this solution would require Formula One for Java to keep nine separate formulas in memory. A better way to do it would be to enter the formula =$A2*B$1 in cell B2, and use the worksheet's Edit > Fill command to fill cells B2 through D4 with copies of that formula. When the formula is copied in this manner, its relative references change, but the absolute references stay the same.
The resulting worksheet calculates all the figures using multiple copies of that one formula. Only one formula must be kept in memory.
If you change the percentages in row 1 or the figures in column A, the calculations in the worksheet will automatically change because of the absolute references to those cells. This makes the worksheet easy to update. References to Other WorksheetsYou can reference cells in other worksheets in the same workbook by placing an exclamation mark between the sheet name and the reference. The sheet name is the name found on the worksheet tab. For example, Data!A1 refers to the top left cell in a sheet called Data. Sheet names with spaces. If the sheet name contains spaces, you must enclose the name in single quotes: `1994 sales'!B17. Cells on two worksheets. You can make a reference to cells on two different worksheets by placing a colon between the two sheet names. For example, Sheet1:Sheet2!A1 refers to two cells: cell A1 in Sheet1 and cell A1 in Sheet2. Order of sheet names. References to more than one worksheet must list the worksheets in the order in which they appear in the workbook.
References to Other WorkbooksReferences that point to cells on worksheets in other workbooks are called external references. An external reference is created by placing the workbook name in brackets, followed by the worksheet name, an exclamation point, and finally a cell or range reference.
External references will work only if both workbooks are open in the Workbook Designer. If the referenced workbook is not open when you create the external reference, an Invalid Formula Syntax error message will appear. The following are examples of external references using absolute, relative, and mixed references.
Paths in External ReferencesAfter you enter an external reference, Formula One for Java will change the format of the reference to show the absolute path to the workbook you referenced. For example, say you entered this reference to a workbook named September in the Payroll directory on your C drive: [September]Payroll!C2:C420After you enter that reference, if you return to the cell where the reference was entered, you will note that Formula One for Java has changed it to: `[C:\Payroll\September.vts]Payroll'!C2:C420This absolute path is recorded in the worksheet. If you later move the September workbook, the external reference should still work, as long as you open September.vts in the Workbook Designer at the same time as the workbook that references it. Which reference in a formula refers to a cell or cells in another worksheet?A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate.
When referring to a cell in another worksheet same workbook the sheet reference is always?Referencing a Cell in the Another Sheet. First, you have the sheet name followed by an exclamation sign which is followed by the cell reference. In many cases, the worksheet you refer to would have multiple words in the name. For example, it could be Project Data or Sales Data.
How do I reference the same cell from multiple worksheets in Excel?Click the tab for the first worksheet that you want to reference. Hold down the Shift key then click the tab for the last worksheet that you want to reference. Select the cell or range of cells that you want to reference. Complete the formula, and then press Enter.
What term is used when referencing cells in other sheets?There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.
|
