Excel Issue

[lrevellese]

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???

 

[mngl1500]

What are his holdups about converting it to a real database?

Is it cost of database sfotware (sql server express is free and then you can odbc link to it)
Or is it a re-training the staff?

Or a quick google search shows software you can try/buy that reduces the amount of formatting to remove the problem.

 

[lrevellese]

well the software didn't work its just seems to be the sheer volume of information...were talking 10000 rows and 40 columns...its huge. And the database hold up isn't necessary cost related...its just this is one of those companies where the user gets what the user wants...or IT is fired

 

[sheesh721]

Is this your error?
http://support.microsoft.com/kb/213904

If so you've reached the limitations of Excel and this is something to show your boss. Here are some options though:
http://www.total.rowing.org.uk/quarrell/QAid/#message
Note:

The "Too many different cell formats" Excel message
If it was not recommended to you by a fellow sufferer, it is very likely that you have found this web page by entering “Too many different cell formats” in a search engine. In that case, you have probably recently encountered that error message while developing an Excel workbook. You have discovered that the workbook is now completely unusable, and you are deeply worried. If so, you can start to relax! Help is available. It is effective and valuable.

One of the nastiest disasters that may occur with a large Excel workbook is to hit the limit (of about 4000) on the number of different cell formats one workbook can have. (You are unlikely to have hit this limit when using Excel 2007. That version increased the limit to 64K, and it should only report the error if a workbook has hit the limit under a previous version of Excel and is then reopened under Excel 2007).

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