+ Reply to Thread
Results 1 to 8 of 8

Extremely long formula

  1. #1
    Registered User
    Join Date
    06-18-2009
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    8

    Extremely long formula

    I just made an accounting spreadsheet using Excel 2003 with one worksheet each for 50accounts. Every cell in the summary worksheet sums the corresponding values from every account. I was recently asked to add 30 extra accounts in to prepare for future changes in accounting. After adding the 40 worksheets, I went into the summary worksheet and pasted the new formula (written seperately) into the cells. I have tested this several times, and the formula is always summing from the new worksheets. However, I recently discovered the following:
    • In these cells, the formula is cut off, sometimes in the middle of a worksheet's name.
    • If I add anything to the formula, Excel notifies me that the formula is too long.
    • Despite this fact, the formula is still working properly, and summing from all 90 worksheets.

    I am thankful that the formula is working, but I am worried that they may be fragile, and someday unexpectedly stop working. Is this something I should worry about?

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Extremely long formula

    Excel 2003 (and earlier) is limited to 1,024 characters for formulas.

    Perhaps if you posted a sample workbook or some of the formulas they could be shorted or suggestions given on a more efficient approach.
    Last edited by Palmetto; 08-03-2009 at 02:21 PM. Reason: clarity

  3. #3
    Registered User
    Join Date
    06-18-2009
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Extremely long formula

    I was already aware of the character limit. That's why this is confusing.

    Here is one example of a formula:
    Please Login or Register  to view this content.
    The worksheets named "Blank" go all the way up to 30, so it should end in:

    +Blank26!AN7+Blank27!AN7+Blank28!AN7+Blank29!AN7+Blank30!AN7

    Sometimes the formula cuts off at other points, sometimes as early as Blank21, but as I said before, even thought the formula viewer does not mention the last few worksheets, the cell values reflect the values in the excluded worksheets. The formula is doing exactly what I want it to do. My only question is whether this is a bug, and if so, should I worry that sometime in the future Excel will re-evaluate the relationships and decide not to include references to every worksheet.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Extremely long formula

    If you are adding the same cell on every sheet, then you can use SUM and just refer to the start and end sheets:
    =SUM('051858 (SRTR):Blank30'!AN7)
    Remember what the dormouse said
    Feed your head

  5. #5
    Registered User
    Join Date
    06-18-2009
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Extremely long formula

    I did not realize that you could do that. Thank you.

    I tried doing that, though, and I'm getting data-type errors (#VALUE!). How am I supposed to do this?
    Last edited by chcwebb; 08-03-2009 at 03:36 PM.

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Extremely long formula

    In checking the length of the formula, minus the equal sign, I get 1022 characters. So, you're at the limit.

    Given this is for Acconting, I would not depend on this formula as I think you definitely encounter issues sooner rather than later.

    You could try naming the cells you want to add and shorten the formula considerably.

    Say 051858 (SRTR)'!AN7 is named "srtr" (w/o quotes)
    and 052492 (Measures)'!AN7 is named "measures"

    Then
    SUM(srtr,measures)

    Keep the list of named cells on a separate sheet so you don't lose track and can manage them.

    Edit: Romper has the right idea. I'd forgotten about that approach.

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

    Re: Extremely long formula

    check your sheet names are correct including any spaces
    "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

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Extremely long formula

    I think it might be worth checking each sheet to see if you have underlying error values present in the common cell being summed...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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