Excel Issue

[lennyrevellese]

Hey Guys,

I just recently started my career in IT(about 3 years now, 1 year in corporate) and I seem to be at a crossroads with an Excel Issue. It breaks down like this...

The office is basically using excel as a database

They do not want to use any other program because of ease of use and familiarity

The errors started after a switch to office 2007 from 2003. Everything was going fine up until a month ago. The first errors that were popping up were the "too many formats" error. Excel would then try to rebuild the worksheet, poorly, and most of the data and calculations would be lost.

One of my coworkers decided to take a shot at it (basically bypassing me) and ordered a program to dumb down and alleviate any duplicate formats. The resulting error is now the loss of the link to the pivot tables in either the same or a different workbook on the shared drives.

So my boss wants an answer by the end of the day. I told him day one this would not work and a database would be the only solution.

HE TOLD ME NO...

He wants me to find a solution to a problem that has no solution. They have reached the functional limitation of excel, which is understandable because it is not a database.

Unfortunately everything I have tried, all of my coworkers and friends I have called, all of the information I have on the program... None of this information includes a answer that still allows the use of excel...

What do I do???

 

[nickbits]

Use less cell formats?

 

[lennyrevellese]

haha yea i said to do that but its not my sheet its for the company i work at and god forbid its not pretty colors and fonts. When i ran the progra the company purchased it said i only have 800 formats and it cut it in half...im still getting the error because of the pivot tables...this sucks

 

[PowerEngineer]

You've probably already googled the problem yourself, but it appears ther are some people out there that have cobbled together some tools to attack the problem (and you may have already tired one?). But just in case, here's a couple of links to explanations or tools that look helpful:

http://support.microsoft.com/kb/213904

http://xlsgenreduction.arstdesign.com/index_en.html

http://www.rowingservice.com/quarrell/QAid/

What's a bit puzzling to me is that the limit on cell formats was raised from 4000 in Excel 2003 to 64000 in Excel 2007, so you'd think a spreadsheet that "fit" under 2003 would have no issues in 2007. Maybe you can try saving it with an .xlsx extension?

Any chance you can see how the spreadsheet works in Excel 2003?

Good luck! It sounds like I should hope we don't move to Office 2007 anytime soon!

 

[GaryJohnson]

haha yea i said to do that but its not my sheet its for the company i work at and god forbid its not pretty colors and fonts. When i ran the progra the company purchased it said i only have 800 formats and it cut it in half...im still getting the error because of the pivot tables...this sucks

Why not open it in 2003, save it as a csv (which will strip all the formatting), then open the csv in 2007 and try and replicate the formatting.

And excel can link to a database. Migrate everything to an access database and give them an excel file that's linked to it. They'll never know the difference.

 

[imagoon]

What do I do???

Personally I would print or link the tech note from MS showing that Excel does not support what they are doing. Suggest that you can devote a minimal amount of time to remove formatting and try it out. However, formatting is not really an "IT issue" it is a user / operations issue so you are most likely do not have the right knowledge to fix this on your own. If you boss fails to understand this, then well welcome to the club. Another solution could be to pay MS the $250 / hour they offer for a customized version of Excel that does support this. I would expect potentially thousands of hours for it to be done.

Another random thing, I know Word would save "undo" data in the .doc(x) I believe it was one of the reason why long life, high edit .doc(x) files liked to grow in size. Maybe a "save as" to excel 2003 or 2k would strip it out? I honestly don't know for sure.

 

[imagoon]

Why not open it in 2003, save it as a csv (which will strip all the formatting), then open the csv in 2007 and try and replicate the formatting.

And excel can link to a database. Migrate everything to an access database and give them an excel file that's linked to it. They'll never know the difference.

This will also strip all the formulas. I know you can export the formulas but I am pretty sure you cannot export the pivot table and the like so it would need to be rebuilt by hand.

 

[lennyrevellese]

thanks guys im waiting for my companies Microsoft people to get back to me on the issue but i will definatly take this info into consideration (and use it as an advantage) to get a database. This is the department i support and ill be damned if ever 2 months i have to hear the same problem.

Thanks again

 

[GaryJohnson]

This will also strip all the formulas. I know you can export the formulas but I am pretty sure you cannot export the pivot table and the like so it would need to be rebuilt by hand.

That's accurate, but if you're other option is to migrate from a spreadsheet to a database you're going to have to do that anyway.

You could export to CSV, delete all the data in the excel 2003 file, open the excel 2003 file in 2007, then import all the CSV data back in.

Or, just give the users the data in the new excel file and let them create their own damned pivot tables pivot tables are a feature of the presentation software, which is something the end users should be proficient with.