Apply Name to a formula in the Excel Name Manager rather than to a range of cells

Hello,

How can a Name in the Name Manager refer to a formula rather than to a range of cells?

I am comfortable using the Name Manager in Excel (2010) but I have not previously come across a Name which refers to anything other than a range of cells. However, I recently opened an old amortization schedule template which I downloaded or received from somewhere/someone many years ago.

Interestingly, in the first row of the schedule, the formula under the heading Beginning Balance is as follows:

        =IF(Values_Entered,Loan_Amount,"")

That seemed odd because rather than some type of criteria or question as the first argument, it simply displays what appears to be a named cell range. Therefore, I opened the Name Manager and that's when I discovered that rather than defining a range of cells, the name

        "Values_Entered"

Refers to

        =IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)

which seems to be a way of checking that each of the included ranges is populated.

Is that interpretation correct?

Questions:

1) Is the name "Values_Entered" and its corresponding formula something that is built into Excel or something that was created and defined by the person who built the amortization template?

2) If the former, how does one access it and similar built-in named formulas in Excel?

3) If the latter, how does one go about creating additional named formulas in Excel?


Thanks
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
"1) Is the name "Values_Entered" and its corresponding formula something that is built into Excel or something that was created and defined by the person who built the amortization template?"

The latter; defined by somebody involved with the creation/development of the workbook (template).

"2) If the former, how does one access it and similar built-in named formulas in Excel?"

Names (named ranges) can hold many functions/formulae; they are not restricted to just cells/ranges.

"3) If the latter, how does one go about creating additional named formulas in Excel?"

Via the Name Manager, as you had seen, or programmatically via Visual Basic for Applications code statements.

For your information:

"Define and use names in formulas"
[ https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64 ]

Look towards the bottom of the article for text regarding values & 'RefersTo' syntax.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeThotUWasAToadAuthor Commented:
Many thanks for the response.

The way I see this is that it essentially gives one the ability to create and call subroutines, if desired, to simplify complex or large and confusing Excel formulas.

Thanks again.
0
[ fanpages ]IT Services ConsultantCommented:
Yes, that is one application for the feature.  The "hidden" functions can also be used locally, within one workbook, differently depending on which worksheet is active by the definition of the same name on individual worksheets, or globally across the whole workbook.

I use names (named ranges) in this manner in some projects to "hide" typically static, but potentially variable, configuration settings away from the main worksheets, so they can be changed & the run-time operation of the workbook's functionality can be amended, either during processing, or upon the next time the associated workbook is opened.

An example of a static setting may be the revision number of the workbook, or some other defining characteristic.

A variable setting may be a folder where files should be stored, or where input data is taken, that may not change too often, but can be changed without showing this information to the run-time users (or storing with Visual Basic for Applications where specific skills are required to make such a change internally).
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.