Page 29 - TP_Play_V2.1_class6
P. 29

Mixed References

                 A mixed reference is a reference that refers to a specific row or column. It is basically a combination
                 of relative and absolute references. In some situations, you may need to make the cell reference
                 as ‘Mixed’ reference, so that you can lock either a column or a row by preceding it with a Dollar
                 ($) symbol.

                 For example, $A1 or A$1.
                 References to Other Worksheets


                 You can refer to cells in other worksheets within the same workbook just as easily as you refer to
                 cells in the same worksheet.

                 For example, to enter a reference of cell A2 in Sheet2 into cell A1 in Sheet1, do this:
                 Step 1  Select cell A1 in Sheet1, and type an equal (=) sign.
                 Step 2  Click the Sheet2 tab.

                 Step 3  Click cell A2, and then press Enter key. When you press Enter key, Sheet1 gets active.
                 Step 4  Select cell A1, and you will see that it contains the formula = Sheet2! A2.

                 The exclamation point separates the worksheet name of the reference from the cell address.
                          Tech Funda




                       Pressing F4 key repeatedly over a reference (cell) will allow you to switch between
                       making the column, the row, or nothing ‘Absolute’.




                 Other Workbook References

                 You can refer to cells of a worksheet in other workbooks in the same way you refer to cells in
                 other worksheets within the same workbook. These references are called external references.
                 For example, to enter a reference to Book2 in Book1, follow the given steps:
                 Step 1  Create two workbooks named Book1 and Book2.

                 Step 2  Select cell A1 in Sheet1 of Book1, and type an equal (=) sign.
                 Step 3  Switch to Book2 by pressing Alt+Tab keys from the keyboard. Click to select cell A2.
                 Step 4  Press Enter key.
                         After you press Enter key, your formula in Book1, Cell A1, should be
                         = [Book2] Sheet1! $A$2

                 This reference has 3 parts:
                 1.  The workbook name: Book2 in square brackets
                 2.  The worksheet name: Sheet 1 followed by ! sign
                 3.  The cell address: having absolute reference

                 Hence, referencing cells in the external workbooks are done by selecting the workbook, then
                 worksheet, and then the cell you want to link reference to.

                                                                                     Formulas and Functions       27
   24   25   26   27   28   29   30   31   32   33   34