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

Users are saying about us...

  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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube