Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Corrupt Defined Name Formula (Excel 97-SR2)

    Hi There

    I have a fully working formula defined as a name (Insert, Name, Define...) I wanted to create a variant of this formula so I thought I would copy the formula to a cell in teh worksheet and edit it there...... Except.... most of the formula is missing? But it works????? Weird...

    Tried moving it Excel 2000, no joy.... Tried using Jan Karel's name manager V2.3 build 410 - It too can only see a small part of teh formula...... But the formula works?

    Any way I can rescue a copy of the formula... It's complex and would rather not have to re-create it if I can avoid it.

    Regards
    Peter

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Defined Name Formula (Excel 97-SR2)

    Can you post a (suitably edited, if necessary) version of your workbook so that we can have a clearer understanding of your request?
    Gre

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Defined Name Formula (Excel 97-SR2)

    Hi There

    I can't easily strip out the part that's causing problems... (Sorry, that sounds ungarcious, not intentional..) I have however managed to recreate the formula (phew!). This is what it should be:

    =IF(AND(ClientCkts!$B2>0,ClientCkts!E$1<>""),COUNT IF(INDEX(tblClientBW,0,ClientCkts!E$1),"<"&ClientC kts!$B2+1)-COUNTIF(INDEX(tblClientBW,0,ClientCkts!E$1),IF(Cli entCkts!$A2=calFirst,"=0","<"&INDEX(tblTCkts,INDEX (lstValid,MATCH(ClientCkts!$A2,lstValid,1)-1),idxBandwidth)+1)),0)

    This is what the defined named "calClientCkts" thinks it now is:

    =AND(ClientCkts!$B2>0,ClientCkts!E$1<>"")COUNTIF( INDEX(tblClientBW,0,ClientCkts!E$1),"<"&ClientCkts !$B2+1)

    I have a work around for now, but I'm just curious as to why the behaviour is exhibited? In particular why, if that's what Excel 'says' what the formula is, why it still works and displays the correct results?

    Regards
    Peter

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Corrupt Defined Name Formula (Excel 97-SR2)

    If you get the refersto property of the names object what does it display?

    I think the refersto property can only be 255 chars long.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Defined Name Formula (Excel 97-SR2)

    Thanks Steve

    Not certain how I would get the refers to property, and don't have time to play just now.. (Though I will look it up later..) ... But given the number of characters in the formula, 276, your suggestion as to the problem seems a very plausible cause of the issue. I'll shorten the variable names and see if I can squeeze it in in the interim.

    Edited - You were correct Steve... I changed the name of the worksheet ClientCkts to CCkts, which makes the LENght if the formula 241, and the defined name magically re-appeared! Thanks again for your assistance.

    Regards
    Peter

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Corrupt Defined Name Formula (Excel 97-SR2)

    Glad it worked out:
    To answer the first question.
    Goto VB (Alt-F11)
    Immediate window(ctrl-G)
    enter:
    <pre>? activeworkbook.names(DefName).refersto</pre>

    where "DefName" is the defined name of the range of interest.

    Note you can set it in the immediate window also, though it gets difficult if it has quotes in it.

    Steve

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

    Re: Corrupt Defined Name Formula (Excel 97-SR2)

    Yes, the name manager also chockes on names with a refersto longer than 255 characters. I am trying to solve that issue in the next version.

    DOwnload version 3 at:

    http://www.jkp-ads.com/NameManager.zip (255 char issue not yet resolved)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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