Page 41 - ModularV1.1 _c7_flipbook
P. 41

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 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 press the Enter key. When you press the Enter key, Sheet1 is now active.
                 Step 4  Select cell A1 and you will see that it contains the formula = Sheet2! A2.

                 The exclamation point separates the worksheet portion of the reference from the cell portion.

                  Tech


                    Funda
                                  Pressing F4 key repeatedly over a reference (cell) will allow you to switch
                                  between making the column, 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 these 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 the Enter key.
                         After you press the Enter key, your formula in Book1, Cell A1, should be

                         = [Book2] Sheet1! $A$2
                 This reference has three parts:
                     1.  The workbook Book2 in square brackets

                     2.  The worksheet Sheet 1 followed by ! sign
                     3.  The cell 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  39
   36   37   38   39   40   41   42   43   44   45   46