WeThotUWasAToad
asked on
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_Am
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_R
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
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_Am
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_R
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
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).
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).
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
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.