You can use names in formulas and functions by:

  • Typing the name.
  • Pressing F3 -- or the Insert → Name ? Paste command -- and selecting from the Paste Name dialog box
If a name exists only once in a workbook, the name is specific to that location in the book and is available "book-wide." If a name exists on more than one sheet, the name becomes sheet specific. It ends up working just like you would hope it does!
If you use the F3 -- or the Insert ? Name ? Paste command, you will see a list of names available for the current worksheet. If you paste a name that exists on more than one worksheet, be sure you know which name has been used. Better yet, use your mouse to select the exact reference or type the name using the syntax: 'SheetName'!Name