Excel 2010 vs 2000 - Too Many Formats

[billfinn]

I've spent the last three months working on this project and am getting down to the short strokes. I asked my boss to take a look at what I have today and he got an error message that there are too many formats. I found out that while I have been creating this with Excel 2010 most others in the company are using Excel 2000?!?!? I've been deleting formats in the parts of the project that I inherited but I how do I find out if I still have too many formats for Excel 2000? There is also the issue that my macros may or may not work in his version.
I want to go home early and touch up my resume...
Any suggestions or thoughts are very much appreciated.
thanks,
Bill

 

[Macropod]

See: https://support.microsoft.com/en-us...different-cell-formats-error-message-in-excel
Essentially, the problem is due to the workbook containing more than about 4,000 different combinations of cell formats. That's the limit in in Excel 2003; presumably the limit is similar in Excel 2000; Excel 2007 & later support as many as 64,000 different combinations.


You may be able to resolve the issue via the RemoveStyles addin, at: https://archive.codeplex.com/?p=removestyles

 

[Joe4]

Another piece of advice that won't help you now, but maybe in the future - if possible, find out the lowest/oldest version of Excel that your users will be using, and do you develop on that version of Excel (i.e. Excel 2000 in this instance). Most of the time, things created on lower levels will work on the higher levels, but not necessarily vice versa (they tend to add new functionality a lot more than they take it away).

 

[billfinn]

Macropod,
Thanks much! I had actually run across that XLStylesTool in a search early this morning and used it. Great tool and easy to use. Big improvement in number of styles.

Joe 4,
Thanks much! Yeah, I unfortunately assumed that since all my previous employers had everyone on the same version of Excel that this one would as well. Now I have to pay my stupid tax and reformat a bunch of stuff.

Does either of you know of a list of Styles and perhaps examples? I ran a macro to identify the remaining styles in my workbook after running the XLStylesTool and came up with 117 remaining. Cell Fill Colors are obvious but I don't necessarily know what they all mean, things like Comma 2 2, Percent 4 2. I can assume that Percent 4 2 means that the percentage will display as 4 digits to the left of the decimal and 2 to the right but I already had enough assuming on this deal lol.

Thanks much to both of you for your help
Bill

 

[billfinn]

Incompatible formats between Excel 2000 and 2010.

I'm sure I'm not the first or the last but I had to redo the project today as an Excel 2000 sheet because that is the version they support. I already gave myself the lecture about doing projects in the oldest version I need to support. I ran into the dreaded incompatible formats gotcha. I I have been through my 40 sheet, 40,000 formula sheet way too many times. I got it down from 283 cells with incompatible formats to just 2. Do you think I can find those 2? Nope. Wondering if anyone has a sneaky way to find the incompatible formats. I pulled the 2010 version up on my PC and the version I saved in 97-2003 version on a laptop right beside it. I can't spot anything that is different in background colors, cell formats or anything else. I selected whole sheets and set them with standard formats. I'd like to ignore them but someone will open the sheet with a newer version and try to save it, then scream at me about the message.
Any thoughts or suggestions very gratefully considered.
thanks,
Bill

 

[Macropod]

Re: Incompatible formats between Excel 2000 and 2010.

You might try saving the workbook as an Excel 5.0/95 workbook. If it then opens on the Excel 2000 platform without causing errors, you could then save it back to the 97-2003 format.