Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    Create Local Range Name
    Quick way to do a sheet-local rangename
    Sheet-local range names are very useful in some kinds of models.

    What is a Sheet-Local Range Name?
    • It is a range name that you can use in your formulae like any other.
    • It can only be used on the sheet where it is defined. Using it anywhere else causes a #NAME? error.
    • You can have the same name on different sheets.

    Why are they useful?
    The last characteristic means that if you have a sheet which does a certain job in a modular fashion eg calculate a loan or a lease, you can copy that sheet over to another sheet, and when all the formulae are copied, the range names and the range definitions will be copied as well.

    How do you define a local range name in Excel
    To define a sheet-local range name, when you come to name the range, you simply preface it with the sheet name eg MySheet!MyName, and then you can use MyName anywhere on that sheet.

    For the technical: Why is this possible at all?
    This functionality is possibe because in Excel, a name range is actually a name attached to a formula. Prefacing a range name with a sheet name is a perfectly valid cell reference, and therefore a valid formula. What Excel does which is clever is the copying over thing, where the local range name definition is copied to the new sheet.

    So what does the BF macro do?
    The "Create a Local Range Name" option in BF simply puts in the sheet reference for you, not much more than that. Because I always forget how to define local range names (remembering to put in an exclamation mark eludes my fading memory!), I find this utility useful. Whenever you are building sheets that do a certain job that yuo want to replicate to other sheets, think about local range names.

Business Functions Ltd, London, UK Website Design: Webpure