Hi, I have found an annoying issue that i hope has an easy solution:
My spreadsheet has one column that has a formula that relates to most other columns…ie it grabs a percentage (tax) from other columns. If i want to move an entry value from one column to another due to a mistake in entering it, the tax column doesn’t forget the original value so it doubles it! Deleting all contents does not help. The only work around which is unsatisfactory is to type it manually into a new cell and then change the original to a zero. any suggestions for speeding this up?
If do you move* a cell content that is referenced in a formula, the formula is updated.
Instead of move, you can copy the misplaced value, delete it and paste it in the right place.
This is not clear to me.
Maybe you can edit your question and share a reduced sample file with explanations.
* or cut and paste.
1 Like
Thanks for replies. I’m using latest update and windows 10.
Try again: I have a column whose cell’s formular references cells in many columns adjacent. Normally there would be only one cell with a value in each row but the function adds all the cell values in the row (the rest being zeros) because it can’t predict which column might have the value to reference. The function makes a % of the values in each row.
If I enter a value in a wrong column, cutting and pasting to the correct column gives the function cell twice the % value it should have. If I manually type in the value to the correct column then type a zero in the wrong column the function is correct. Seems like a bug??
Please provide a sample .ODS file.
It very much depends on what formulas you have used. Also note very well @LeroyG comment that if you cut a cell and paste it then formulas that reference the cell are updated to point to the new position. This means that in a cell that has a long formula that references each cell separately, there could be two references to the same cell after cutting and pasting
Thanks…that explains it…the function cell adds another value each time a cell content is cut and pasted to another referenced cell. This seems silly doesn’t it? Any work around other than manually retyping the entry and replacing the wrong entry with a zero?
I expect there are different ways the formula could be modified but we don’t know how your spreadsheet works
No. This is the expected behavior: that you can reorder some values and the formula continue to be valid.
A workaround to this problem is to copy the formula from a cell without changes, and paste it in the cell with double references.
Using the correct built in function might avoid the appearance of the error but cutting and pasting will always change references, in this case damaging the spreadsheet.
Normally updating references when cutting and pasting is useful. Imagine you have a spreadsheet with one cell containing the GST percentage and many cells referring to it. Now for some good reason, you need to change its position, cutting and pasting it elsewhere will maintain all those links from dependant cells.
Provide an example of the spreadsheet, it will be easier to give useful suggestions.