Named Range Using Table References

[Ada01]

Hello

I am trying to create a reusable calculation for loan payments to try to shorten the length of the formula and bring a little clarity to what is happening in the calculation.

i have this formula nested in the remaining formula and would like to place it as a named range ("variable_interest_payment") but i have no idea how or what combo of quotes or apostrophe is needed.

Please Login or Register  to view this content.

I have two of these formulas nested in the main calculation in order to calculate the payment for either a fixed or variable interest. I want to shorten the formula in the way mentioned to more easily handle some deferment calculations to be added to the workbook.

Thanks

Adam

[Greg M]

Hi there,

I may be wrong, but I don't think Table notation ([@[Field Name]]) can be used outside a Table, so it probably can't be incorporated into a defined name formula.

Regards,

Greg M

[jason.b75]

but I don't think Table notation ([@[Field Name]]) can be used outside a Table

It can if you qualify it with the table name, not sure about named ranges though.

Table1[@[Field Name]]

[Greg M]

Hi again,

Jason: many thanks for that useful information. I very seldom use Tables, so I'm not up-to-speed with all of their possibilities

Ada01: thanks to Jason's post you should be able to define the worksheet-level named range formula "variable_interest_payment" as:

Please Login or Register  to view this content.

I've assumed that the worksheet is named "Sheet1" and that the Table involved is called "Table1" - otherwise change those values as appropriate.

If it were me, I'd use the Name "forVariableInterestPayment" (for = formula) so that what it refers to is a bit more obvious when looking through the list of defined names in the Name Manager, but that's very much a personal preference.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Ada01]

@Greg M

Hello,

I was hopeful that it could be used in the same way that

Please Login or Register  to view this content.

can work inside of a Data Validation List

Thanks for your insights

[Ada01]

@Greg M (Again)

It seems to be working. The "Sheet1" was not needed as the references there are fixed single cell ranges. I have been able to adapt this quite nicely throughout my workbook and the end client will be very pleased with the results! I am even finding the formula to pass through OK to the UserForm I am developing,.

Thanks for your insights and this currently quite successful solutions. I love when they are easy, but the obviousness of the solution is so obvious you just want to hide under a blanket from embarrassment.

Thanks Again

Adam

[Greg M]

Hi again Adam,

Many thanks for your feedback - glad to hear that the proposed solution seems to be working for you.

The "Sheet1" was not needed as the references there are fixed single cell ranges.

I had assumed that the "payments_per_year" and "total_loan_pmts" named ranges had been defined at worksheet level rather than at workbook level. In such a case (worksheet level) it wouldn't have been necessary to include the "Sheet1!" prefixes when DEFINING the named range formula, but if you looked in Name Manager afterwards you would see that the prefixes had been added automatically.

If the named ranges are defined at workbook level, the name of the workbook will be added automatically as a prefix wherever appropriate within the named range formula.

You're very welcome - delighted to have been able to help out.

Regards,

Greg M