Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    words instead of cell numbers in formulas (2000)

    I use Microsofts loan amoritation template for loans. The shortcoming is that it assumes the payment was made on time. I want to modify it so I can add the actual payment paid. The question is, the formulas use words in reference to cells, rather than the normal identification.
    I have attached the template to this, which is the standard amoritization table with my new collumns added ("actual payment date, actual payment received, and late pmt").
    To see what I mean, go to cell I18. The formula makes reference to "Sched_Pay", instead of saying D18, which is the cell its referring to.
    1- How does one define a group of cells like that?
    2-How can I change that to refer not to Scheduled Payment (column D) and refer to my new column (F) of Actual Payment received.
    Thanks,
    Rick

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: words instead of cell numbers in formulas (2000)

    Select Insert | Name | Define... to see a list of defined names in the workbook, and their definitions. A defined name can refer to a range (such as Sched_Pay - it refers to D18377), but also to a formula (for example Last_Row - it refers to the formula =IF(Values_Entered,Header_Row+Number_of_Payments,H eader_Row))
    You can create a new name Actual_Payment referring to $F$18:$F$377. You can then refer to Actual_Payment in formulas.

    There are several Excel add-ins that make working with defined names easier, for example Name Manager from WMVP/Excel MVP <!profile=Pieterse>Pieterse<!/profile> and Navigator Utilities from <!profile=MarkWW>MarkWW<!/profile> (links in their profiles)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: words instead of cell numbers in formulas (2000)

    Thanks!!!
    I'm always amazed at how much you guys know.
    Rick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •