Excel error - "Too many different cell formats"

Discussion in 'OT Technology' started by dissonance, Feb 15, 2008.

  1. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    Excel 2003 btw.

    Has anyone run into this problem and found a fix for it?
    [​IMG]

    I know this is Microsoft's response:
    http://support.microsoft.com/kb/213904

    And so I could use Conditional Formatting to get some more out of Excel but would run into to this eventually (plus, I'd rather not use conditional):
    http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;215783

    So far I have only found one possible way to help, but it's far from a fix and isn't free: http://xlsgenreduction.arstdesign.com/. Anyone used this before?


    EDIT: And I have 401MB out of 1039MB available RAM (according to task manager)
     
    Last edited: Feb 15, 2008
  2. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    I've never seen that error before. Did you make a disco-ball spreadsheet or something? Maybe you just need to make it less pretty and more useful.
     
  3. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    It is quite useful. A good 90% of the formatting is just color coding oem's and then making things either percents, dollars, etc. I guess even the graphs count toward the formatting limit. I kind of have to make certain items pretty looking as they are what executives look at. Oddly though, in the past month or so I have been consolidating and cleaning out the workbook and it has dropped in size from ~3.5MB down to ~2.7MB and this is now happening.

    I must say it is the most frustrating thing I have had to deal with in regards to Excel. The error message even appears and prevents me from taking away formats.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You could split the workbook up into multiple books (executive view for example). You can still reference each book within other books.
     
  5. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    Thought about that. The only issue is the way things are done around here. Each day you modify a workbook, you change the file name to xxx_15feb_2008 and put the old one in an 'old' folder. So then the links would need to be modified all the time. I did think of a way around this but not sure if it will be accepted around here. Instead of dating the files, make a folder to keep each version in with the date in its title.
     
  6. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    Well I tried the trial version and that tool is a fucking joke. When I analyzed there were 172 duplicate styles. I hit the 'delete duplicate' button and then when I analyzed again there were 174. Odd, so I tried once more, wtf, now there are 175 duplicates. I then tried the compression button and it did absolutely nothing to help. After compressing it went from 3618 to 3645 styles


    :squint: :squint: :squint: :squint:
     
  7. trouphaz

    trouphaz New Member

    Joined:
    Sep 22, 2003
    Messages:
    2,666
    Likes Received:
    0
    wow, that's a horrible practice for a naming convention. in general i've seen that the current file has some name, xxx in your case, and when you go to update it you create a copy called xxx_<current date> and then update the original. then you have the backup right before it changed, yet you have a standard name that you can always refer to for the current info.


    it is possible that the file itself became corrupted. is there a way within excel where you can create an entirely new workbook and import the information from the old one? in a word doc, you can just copy and paste, but who knows with all of the special formatting.
     
  8. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    I must say I would much prefer to do your naming structure.

    Could be, but from what I have found on the internet its likely just the formatting limit. I wish we could upgrade (if you can call it that) to office 2007 and still keep office 2003 because they greatly increased the number of unique cells styles in it.

    I doubt I could just import it to a new one with all the sheet references and formulas. I highly doubt it would import properly. I think I am going to have to break it into multiple workbooks and hope the unused styles clear from file.
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yup, use the folder name as the dating mechanism - all books go into each folder, each folder represents a date, then its parent could be a month, and its parent could be a year.

    If you wanted you could even have a script run at the end of the day to archive the file accordingly.
     
  10. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    sounds like it's time to upgrade to a better analysis tool. Perhaps make something web-based.
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    tru.dat

    Excel is a great tool but it does make me weep when it's used in this fashion.
     
  12. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    Well, most of the work I do (price analyst) isn't this large nor complex. But this model was developed for one purpose. Then it was presented to management who wanted it to do X other things. Then it was presented to them again and they wanted some more. More recently it was presented to executives who now want it to do Y other things. The damn thing just keeps growing exponentially. It is the first project I started when I got my job and it goes away for a few weeks and then comes back.
     
  13. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    If you tell them you knowthe status quo but it just doesn't work, what can they do? It's not worth the money to pay you to slave away for days or weeks trying to make it perfect.

    Also, does the limit apply to all sheets in the file combined, or each sheet? If you can split the spreadsheet into multiple sheets in the same file, then it doesn't matter what the file is named, the links will still work.

    Finally, consider putting the data into Access and generating reports instead. There's a reason why no big companies use spreadsheets for anything serious anymore.
     
  14. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    The limit is per file, so all sheets in the workbook are counted in the limit.

    Access is an idea, I haven't used it in several years.
     

Share This Page