+ Reply to Thread
Results 1 to 9 of 9

Is there a limit to defined names?

  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    Wellington
    MS-Off Ver
    Excel 2010
    Posts
    20

    Is there a limit to defined names?

    Hi, I am preparing a graph and need to define names (insert - names - define) in exel 2003. I hold down ctl and go B5-B11, C5-C11 etc. I need to get to column AA but it won't let me go further than col N. Is there a way around this? I have attached the spreadsheet. thanks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Is there a limit to defined names?

    i use 2007 and it worked ok for me. but maybe you could try to google it, if no1 else here can answer your question?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-16-2012
    Location
    Wellington
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Is there a limit to defined names?

    Hi FDibbins, I did a bit of a google search before posting but didn't come up with anything - maybe I used the wrong terms. I'll wait a bit longer and then try googling again. thanks.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Is there a limit to defined names?

    Hi,

    Why are you separately referencing every column in the definition?

    Just define the name as being Sheet1!A5:AA11
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Is there a limit to defined names?

    that formula is 250 chars long i think the limit 255
    i just googled this http://www.thenakedpc.com/articles/v02/07/0207-04.html
    One potential annoyance: the Refers to text box suffers from a limit of 255 characters so if you have a very complex set of range coordinates you might exceed this limit. Excel just truncates your reference without warning, so beware!
    Last edited by martindwilson; 06-10-2012 at 08:09 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    03-16-2012
    Location
    Wellington
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Is there a limit to defined names?

    Quote Originally Posted by martindwilson View Post
    that formula is 250 chars long i think the limit 255
    i just googled this http://www.thenakedpc.com/articles/v02/07/0207-04.html
    Thank you both. Richard I'm simply following the instructions from http://www.thenakedpc.com/articles/v02/07/0207-04.html
    They make a point of doing it this way. This worked well on another smaller set of data and so I wanted to use it on for this too.

    Thanks for the link Martin, I'll try renaming my sheet and taking out the absolute references to see if that reduces the number of characters enough.

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Is there a limit to defined names?

    Hello tier.
    just an idea..

    if ever you could "shorten" the sheet names because this is also being used by the name range.. the we exceed the limit.

    or maybe

    divide the ranges

    say
    rangeA
    ==Sheet1!$B$5:$B$11,Sheet1!$C$5:$C$11,Sheet1!$D$5:$D$11...........

    rangeB
    =Sheet1!$E$5:$E$11,Sheet1!$F$5:$F$11,Sheet1!$G$5:$G$11,S..........

    rangeAB
    =(RangeA,RangeB)
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  8. #8
    Registered User
    Join Date
    03-16-2012
    Location
    Wellington
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Is there a limit to defined names?

    Quote Originally Posted by vlady View Post
    Hello tier.
    just an idea..

    if ever you could "shorten" the sheet names because this is also being used by the name range.. the we exceed the limit.

    or maybe

    divide the ranges

    say
    rangeA
    ==Sheet1!$B$5:$B$11,Sheet1!$C$5:$C$11,Sheet1!$D$5:$D$11...........

    rangeB
    =Sheet1!$E$5:$E$11,Sheet1!$F$5:$F$11,Sheet1!$G$5:$G$11,S..........

    rangeAB
    =(RangeA,RangeB)
    Thanks vlady, i'll try those options as soon as I get some time later this afternoon.

  9. #9
    Registered User
    Join Date
    03-16-2012
    Location
    Wellington
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Is there a limit to defined names?

    Hi vlady, I haven't had any success with your suggestions so far. when I separate the ranges into smaller ones my chart gets very muddled. Since I need this done today I will make two graphs instead of one. Then in my own time I will play around with your ideas. Thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1