Error in formulas

[oldjay]

I posted this in General Questions but didn’t get a satisfactory answer.

I have a workbook that is used for estimating and manufacturing process
control and has been used for many years. My problems have occurred since I
tried to migrate to Excel 2007. I opened the workbook in 2007 and saved it as
an .xls file compatible with older versions. The size of the file increased
from 3.0mb to 3.6mb. It has numerous formula errors.

I have deleted all data and code except for 2 of the many cells with formula
errors. I have saved this as a workbook. It is now has 516k with only 2 cells
populated. I would like someone to look at this and give me some advice on
what to do. Is this increase of 500K normal? Is this a Microsoft problem?

 

[OssieMac]

Hello again oldjay,

Don’t know if any of the following will help but in the absence of anything
else you might like to try it.

Firstly there are some problems with converting earlier versions of xl
workbooks to xl2007. The main one I found was with enabling macros. I found
that I had to open xl2007 and enable macros with notification and then close
and re-open xl2007. Then open the earlier version workbook and save it as an
Excel Macro enabled workbook. Then close the workbook and re-open it in
xl2007. After that everything seemed to work OK.

However, you said that you were re-saving as an earlier version. If you open
an earlier verion workbook and simply Save, it defaults to the version in
which it was at the time of opening. If you have macros, you should still
enable macros in xl2007 first, close Excel and then re-open it before opening
earlier version workbooks. (Once macros are enabled then don't have to do
that again next time you are opening a workbook with macros.)

With a number of formulas with errors, I would be checking out the Add-Ins
that were being used previously. If you still have access to the computer
with the earlier version of Excel, Select Menu Item Tools -> Add-Ins and make
a list of the ones that are checked.

Then in xl2007 select the big Microsoft button. (top left of screen)
Select Excel Options (towards bottom right of dialog box)
Select Add-Ins (left column of dialog box)
Check any boxes that match the Add-ins on the old computer.

If you have not got access to the computer with the earlier version of
Excel, then in xl2007 check the boxes for Analysis ToolPak. It can’t do any
harm and there are a lot of useful functions in it.

If the above does not fix any of the errors, then select each of the cells
in turn with the errors and then highlight the formula in the formula bar at
the top and copy the formula. Then press Esc to get out of the formula bar
and then paste the formula into a post here so we can see what functions are
giving the problem. (Don’t try to copy every cell with errors; just a
selection of them.)

 

[joel]

It is very easy to add features to a database or file system like a PC
but it is very time consuming to get it to shrink back to a smalle
size. There is a lot of checking that has to be performed to determin
how to make something grow smaller. It is a generic problem that ha
been plaguing computer scientist for over 40 years.

There are bugs in microsoft products that never seem to get fixed. On
is like yours that workbooks get corrupted and you can never fix thes
problems. I've gotten errors in a workbook where I deleted every cel
and every sheet and never got the error to go away. An error got stuc
someplace in the internal guts of the workbook and I couldn't get i
removed.

There are techniques to get a workbook to grow samller and I've see
postings about it a different formus. the technique I use is to delet
the unused rows and column on each worksheet. If you add data at th
say row 10000 the workbook size grows (memory) to work with every row u
to row 10000. If you then move you data so it only used up to row 10
and extra memory doesn't automatically disappears. Yo have to manuall
delete the rows 101 - 10000 to get the szie to grow samll again. A
Lewis Carrol said ( a great memthematcian_) IT IS ALL RELATIVE!!!!!

Microsoft doesn't seem to care too much about the amount of memory
product uses. The cost of memroies are cheap, the cost of fixin
software bugs is expensive. It is also expensive to write efficien
programs that don't take a lot of memory. Microsoft is in the busines
to make money. Lots of money.

Look at all the money bill gates has made. After Bill gates droppe
out of MIT going for his engineering degrees he start writing software.
He didn't went to a lot of computer shows and didn't think it was a goo
idea to give away shareware for almost nothing. So he built a compan
that made money by selling software.

You asked the question, I tried to be far and give you a good answer.


I forgot to answer the important question. How do yo fix the workbook
My solution is simple. Right click each tab at the bottom of th
workbook and click move/copy. Press the Copy box and then use th
pulldown to select a new workbook. Then go to all the other sheet
performing the same actions but adding the other sheets to the ne
workbook. You can also select all the sheets at one time and perfor
the same action.

It seems that saving a workbook creates the same internal errors bu
copying the worksheet to a new workbook doesn't copy the interna
errors

 

[oldjay]

Thanks to you and Joel I have solved my problems. The problem with the extra
500k was Command buttons out in the middle of nowhere which, of course, are
not deleted when you delete rows and columns.

oldjay