Single cell formula changes after reopening spreadsheet.

[strongman86]

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.

 

[PhilS2520]

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.

 

[strongman86]

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

<tbody>
</tbody>

 

[MARK858]

Here's example: Column D has got formula applied

How is/was the original formula applied?

 

[strongman86]

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

 

[MARK858]

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.

 

[strongman86]

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=1F1y6nO0wWGG9z5EFHXZFQjBktOG83G30. I have manually added more columns that i'm working with and looks like cell formatting is automatically applied.

 

[thisoldman]

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.