Results 1 to 11 of 11

20081203, 01:14 #1
 Join Date
 Nov 2008
 Location
 Minnesota, USA
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula problem after save (Excel 2007)
I have a user that has an workbook where he has formulas to calculate prices by percentage in one column and another column to show those percentages via another formula. I should also add that he gets all this data first from a .csv file that he imports into Excel and saves it as an .xls file  he then adds these two columns and adds his formula's and copies them down.
This all is fine until he reopens the file later  he makes a change to either of the two formulas and copies down  but instead of the formula being copied down its the resulting value of the beginning cell that is copied instead.
The only way I found to fix this is to copy the whole spreadsheet into notepad and then pasting back into a new workbook  but would like to find out what the cause of this is  anyone have any insight?

20081203, 01:38 #2
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
Can you confirm that the file has no macros enabled?
Regards
Don

20081203, 01:44 #3
 Join Date
 Nov 2008
 Location
 Minnesota, USA
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
Confirmed  no macros.
 Jim

20081203, 01:55 #4
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
Is the problem unique to one user, or to any machine with that file? If the latter; can you post a copy of the problematic file with all sensitive data removed, and also provide specifics about which column(s) are affected?
Regards
Don

20081203, 02:06 #5
 Join Date
 Dec 2008
 Location
 Milwaukee, Wisconsin, USA
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
Is the user just saving it with a file type of Microsoft Excel Workbook or is he just changing the extension from .csv to .xls?
I ran into the same problem with a user and I couldn't replicate the problem because I was changing the file type. However, after watching him do it I saw he did not. After telling him he should save it as an Excel file and not just a file with the .xls extension he has not run into the problem since.

20081203, 02:29 #6
 Join Date
 Nov 2008
 Location
 Minnesota, USA
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
Here is a copy of the file  columns N and O (New Retail and New Discount) are the columns with the formula  I tried this file with my pc  same problem  and the New Discount column should update based on the value of the New Retail but it does not.

20081203, 02:52 #7
 Join Date
 Nov 2008
 Location
 Minnesota, USA
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
I also verified he isn't just changing the extension. I had him walk me through the whole process with another csv file this morning  it all works as it should. Just this file from yesterday is the problem.
 Jim

20081203, 03:14 #8
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
The Calculation is set to Manual. At the moment I haven't determined how to set it to Auto (Thank you Mr. Gates). In the meantime Just hit F9 after Fill Down.
Regards
Don

20081203, 03:24 #9
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
To recalculate all dependent formulas every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic. This is the default calculation setting.
Tip Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.Regards
Don

20081203, 03:44 #10
 Join Date
 Dec 2008
 Location
 Milwaukee, Wisconsin, USA
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
W.D. is right the calculations are Manual. To change it to automatic for just this workbook. You can go to Tools  Options. Then on the popup options window select the Calculations tab. There you can select the "Automatic" radio button.

20081203, 05:24 #11
 Join Date
 Nov 2008
 Location
 Minnesota, USA
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem after save (Excel 2007)
Thank you both for the solutions  works great!
 Jim