Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: limitations within excel!!

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-14 10:51, Chris Davison wrote:
    facilitation of establishing the range dynamically!
    I dunno what this means !

    Me either
    "Have a good time......all the time"
    Ian Mac

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    recently the explaination for:

    =OFFSET($C$1,0,0,MATCH(REPT("z",255),$C:$C))

    is that 255 is the limit for a text string???!! I don't understand why that is!

    as the limitations for a FORMULA are 255 BUT the limits for a text string (according to the help files) is 1024 (this only applies to 97, tested).

    I'm at home now and =REPT("z",30000) seems to cope admirably, i.e. it doesn't return a #VALUE! error. and when I use LEN(A1) on this it returns 30000. so what makes 255 the largest text string???

    I sort of know the answer, BUT to the people it's been given it's VERY misleading, because 255 is NOT the largest.

    any answers????

    _________________
    Share the wealth!!
    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-04-12 01:01 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My mistake on th limits thing, according to the help files the following is the limits that Excel place upon th user,

    Column width 0 (zero) to 255 characters
    Row height 0 to 409 points
    Maximum length of cell contents (text) 32,000 characters
    Maximum length of formula contents 1,024 characters
    Maximum number of sheets in a workbook Limited by available memory (default number of sheets is 3; the maximum number of sheets in a default workbook is 255)
    "Have a good time......all the time"
    Ian Mac

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 01:00, Ian Mac wrote:
    My mistake on th limits thing, according to the help files the following is the limits that Excel place upon th user,

    Column width 0 (zero) to 255 characters
    Row height 0 to 409 points
    Maximum length of cell contents (text) 32,000 characters
    Maximum length of formula contents 1,024 characters
    Maximum number of sheets in a workbook Limited by available memory (default number of sheets is 3; the maximum number of sheets in a default workbook is 255)
    Ian,

    that was me wasn't it ?

    it was *my* understanding of the formula, not a gospel version of the ultimate truth

    It's good to see it being questioned though. Reading through, it looks like you're right, in theory it should be extended to 32,000 "z"s......

    As Mark pointed out though in his original post, this doesn't help the efficiency of the formula : it may be better to incorporate an actual max text string value of the range first.

    HOwever :

    text string "asdfghj", sorted alphabetically, is before "zzz"

    so in a list, zzz will be last (giving us the bottom limit of the range)

    ergo, a string of 255 "z"s would only ever be replaced at the bottom of a list by a string of more than 255 characters, the first 255 of which must be "z"s.

    Since the max column width is static at 255 charcaters, both would appear in a drop down data validation list as 255 "z"s, with the remainder of each being chopped off, visually.

    So it would be pointless having them as the user would not be able to differentiate between the two.

    All of this *assumed*, the user would only ever have a list that had 255 charcters maximum, thus the formula is applicable, even if the explanation is a bit misleading !

    As you can tell, I'm no expert at all, still learning

    cheers Ian
    Chris


    [ This Message was edited by: Chris Davison on 2002-04-13 05:49 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris:
    Practically speaking, for me even the use of a string of 2 to 3 Zs would do it. I think the significance here is on the facilitation of establishing the range dynamically!

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    facilitation of establishing the range dynamically!
    I dunno what this means !


  7. #7
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    865
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    April 14th
    View a collection of recent Excel articles in the Excel Daily News

  8. #8
    Board Regular
    Join Date
    Apr 2003
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: limitations within excel!!

    I have a related problem:

    I have a merged set of cells containing explanatory text. Word wrap is on, but even though there is space for a tenth wrapped row, the ninth row extends, and is thus not visible.

    When I print the document, some of the missing text prints, but not all. About 1250 characters print, the rest are ignored, even if I reduce the font size (not that I want to, but to get round it).

    I can't find any settings that contribute to this, so have had to merge some other cells underneath and experiment with pasting and layout to make the print *look* correct.

    Is there any better method?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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