Single cell formula changes after reopening spreadsheet.
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Single cell formula changes after reopening spreadsheet.

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Single cell formula changes after reopening spreadsheet.

     
    Lads,

    I have formula applied to entire column e.g. =A1+B1. When I change formula in one cell I need and hit enter this new formula automatically applies to the rest of column. Then I hit backspace and formulas on rest of column goes back to normal and my just edited cell has got the new formula which works ok until I save spreadsheet & reopen it and my edited formula changes back to match entire column. I have tried absolute cell reference e.g. =$A$1+$B$1 on this one cell I need to alter and it does the same after reopening sheet.

    Is there any way around this issue? Thanks.

  2. #2
    New Member
    Join Date
    Mar 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Single cell formula changes after reopening spreadsheet.

    Hey strongman86,

    OK, if we go by your example, =A1+B1 then any time A1 or B1 change you will get a new result. This is by design. You are asking to add A1 and B1, and just like in real life, the result will change.

    Try to set the automatic calculations to manual, and see if this helps. But this means you will be forced to issue a recalculate each time you need to, and before saving the workbook.

    I feel there is a design issue here, where what you are trying can be done more efficiently with more knowledge of MS-Excel. Possibly if you can post more details we could provide better answers.

  3. #3
    New Member
    Join Date
    Feb 2017
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Single cell formula changes after reopening spreadsheet.

    Here's example: Column D has got formula applied add adjacent cells from A & B. When I change lets say cell D4's formula manually after saving and reloading it changes back to A4+B4. Turning automatic calculations off is not and option as I need them to be turned on all the time. Thanks.
    A B C D
    1 10 20 =A1+B1
    2 20 30 =A2+B2
    3 60 50 =A3+B3
    4 50 60 =A4+B4
    5 80 66 =A5+B5
    6 44 33 =A6+B6
    7 45 36 =A7+B7
    8 66 44 =A8+B8
    9 33 12 =A9+B9

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,248
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Single cell formula changes after reopening spreadsheet.

    Quote Originally Posted by strongman86 View Post
    Here's example: Column D has got formula applied
    How is/was the original formula applied?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    Feb 2017
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Single cell formula changes after reopening spreadsheet.

    Formula was applied in first cell of the column and excel automatically dragged it down for me.

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,248
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Single cell formula changes after reopening spreadsheet.

    Is it a table i.e. it was a range of cells and you made it a table by selecting the range and clicking Insert - Table?

    excel automatically dragged it down for me
    Excel doesn't automatically drag down formulas without you taking an action either manually or via VBA. Explain more.
    Last edited by MARK858; Apr 15th, 2018 at 10:03 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    Feb 2017
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Single cell formula changes after reopening spreadsheet.

    This is existing spreadsheet I'm working with and it is connected to ''query from MS Access Database''.
    I'm no expert, but I think it's something to do with external data properties. See link. https://drive.google.com/open?id=1F1...ZFQjBktOG83G30. I have manually added more columns that i'm working with and looks like cell formatting is automatically applied.

  8. #8
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,009
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Single cell formula changes after reopening spreadsheet.

      
    Excel likes to put data queries into tables. When you are in one of the cells that is reverting a formula, is the "Design" tab visible in the ribbon?

    You can convert the table to a normal cell range by choosing "Convert to Range" on the Design tab. You probably want to try this first on a copy of the worksheet.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com