Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Northwood Hills, Middlesex, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named Formulae (XP)

    I've created formulae to calculate Actual Years, Months and Days passed between a set date and Todays date (probably reinventing the wheel, but hey, I enjoy a challenge!) - I'd like to include it as a named formula, but as it stands, that involves a lot of syntax - Is there any way to cut and paste the formula into the 'Insert - Name - Define' shortcut

    Thanks in advance

  2. #2
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Named Formulae (XP)

    Try activating the formula bar, so the formula is in Edit mode, select the entire formula, press CTRL-C, press Esc ( to exit Edit mode ), do menu command Insert/Name/Define, type the name you want, activate the Refers To box, and press CTRL-V.

    And hopefully, you have what you want.

    Glenn.

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Northwood Hills, Middlesex, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Formulae (XP)

    Hi Glenn,
    Thanks for the suggestion, but I've been unable to make it work. The 'define name' dialogue box doesn't seem to accept any 'cutting or pasting options'

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Named Formulae (XP)

    That's why you have to use CTRL-V to paste the formula in.

    This is just simple clipboard pasting, and not using any Excel menu edit options.

    Glenn.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Named Formulae (XP)

    Try doing it in VBA. The following code will create a named formula :

    ActiveWorkbook.Names.Add Name:="MyFormula", _
    RefersToR1C1:="YourFormulaHere"

    You could run the code, then when it is working ok, delete the code and save as Book.xlt in your startup folder to have it available to all workbooks you create. You may have to tweak the formula slightly especially if it contains quoter marks ( " ).

    Andrew C

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Formulae (XP)

    You should be aware, that the length of a defined name formula is limited to some 250 characters, whereas a normal cell will accept 1024.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Named Formulae (XP)

    Is it possible that you reinvented datedif?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    May 2002
    Location
    Northwood Hills, Middlesex, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Formulae (XP)

    Thanks for all the comments.

    Oddly, the Ctrl-C suggestion works fine on my XP Pro laptop, but fails on my WIN ME Desktop, I can only guess it is related to a setting I don't understand. (Both systems run Office XP)

    The VB option is fine, and yes, it does look like the datedif will perform the tasks I was after, although my (fairly lengthy) alternative doesn't suffer from the leap year 'quirks' that arise when comparing dates in differing years.

Posting Permissions

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