Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number of characters allowed forFunction arguments (Office97 SR2)

    Hi:
    Here I am with yet another problem. I have attached a spreadsheet file for this one. Here is some background information:
    I am using the following equation:
    =IF(ISREF(INDEX(INDIRECT($AT$3),1,AS5)),INDEX(INDI RECT($AT$3),1,AS5),"")
    to retrieve values from the cells in the named range that appears in cell $AT$3 (the user selects this name from a Validation List). In cell AS5 though AS30, I have numbers, 1 through 22, which correspond with the dates (yy-mmm) for which values appear in each named range (the same for all named ranges).
    Anyway, the setup has been working fine until I discovered that I had to concatenate my range names with another data field in order for them to be unique. This increased the names (doubling or almost tripling them, in some cases). So, now my equation only works for the shorter named ranges. Is there anyway that I can increase the number of characters that this function will accept?
    Thanks for your time and effort.
    Stephen
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of characters allowed forFunction arguments (Office97 SR2)

    Hi;
    I could not edit the post. So, I am attaching the file to this one.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of characters allowed forFunction arguments (Office97 SR2)

    Hi Stephen,

    The attachment doesn't make much sense, there is only a bunch of "used" labels on the Before sheet, and one line of code that applies a range name.

    Anyway, there may be a better approach to what you are trying to do. What is it you have to look up, where, and using what criteria?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of characters allowed forFunction arguments (Office97 SR2)

    Jim:
    Sorry about the attachment. I can send the entire workbook tomorrow, if needed. Anyway, I still have the same question re the equation:
    INDEX(INDIRECT($AT$3),1,AS5)
    If the argument in cell $AT$3 is greater than say 20 characters, it does not work. However, it does work when the argument is shorter. I do not get the same problem with XL 2000 SR1.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of characters allowed forFunction arguments (Office97 SR2)

    Hi,

    Just playing a bit with my Excel 2000, it seems like the INDIRECT function doesn't like commas in the reference text. For example, =INDIRECT(A1) works if A1 contains B1"C1, but not if A1 contains B1,C1 (you get #REF!)

    I can't try anything on Excel 97 until I get home today.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of characters allowed forFunction arguments (Office97 SR2)

    There are no commas. The equation works fine for range names of 19 characters or less and it does not work for range names of 20 characters or more.
    There must be some setting somewhere that governs the length of function arguments. Anyone who knows this, please let me know.

Posting Permissions

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