Results 1 to 11 of 11
  1. #1
    New Lounger
    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?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Silver Lounger
    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

  4. #3
    New Lounger
    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

  5. #4
    Silver Lounger
    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

  6. #5
    New Lounger
    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.

  7. #6
    New Lounger
    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.
    Attached Files Attached Files

  8. #7
    New Lounger
    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

  9. #8
    Silver Lounger
    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

  10. #9
    Silver Lounger
    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

  11. #10
    New Lounger
    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.

  12. #11
    New Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •