Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box

 

Example 1:

Add a formula for calculating the number of the previous year: = YEAR(TODAY())-1
To define a Name that returns the result of a formula:

1. Press Ctrl+F3 for the Define Name dialog box.
2. In the Names in workbook box, type LastYear.
3. Type the formula =YEAR(TODAY())-1 in the Refers to box, and then click OK.

To enter the formula into a cell:

1. Type the = sign, and then press F3 (for the Paste Name dialog box.
2. Select the Name LastYear, and then click OK.

Example 2:
Define a name that stores an exchange rate of 0.82 for 1 Euro vs. 1 Dollar.

To define a Name that stores a value:

1. Press Ctrl+F3 for the Define Name dialog box.
2. In the Names in workbook box, type Euro.
3. Type the formula =0.82 in the Refers to box, and then click OK.
4. Enter a dollar amount in cell A1.
5. In cell B1, type the formula =A1*Euro.

To update a value saved as a Name:

1. Press Ctrl+F3 for the Paste Name dialog box.
2. Select the Name Euro.
3. Change the value of the exchange rate in the Refers to box, and then click OK.
Screenshot // Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box
Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog BoxSaving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box



2 thoughts on “Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box

  1. “Great tip! but….
    Problem- After saving the name and selecting the same, it adds a space between ‘Last’ and ‘Year’ and results in #NAME? I need delete the space in the formula bar, to correct the nuisance. Any ideas on this?
    Thank you!”

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>