Forum Discussion
nunoterrinfra
Oct 16, 2024Copper Contributor
error !REF
Hello guys, when I make my formula for exemple (=A15+0.01) and send it down, when I delete a line the formula becomes an error and displays !REF. Do you guys know any thing that i can put to do not show me again a error ?
thank you for the answer
7 Replies
Sort By
- m_tarlerBronze Contributor
nunoterrinfra well it depends. so I assume that formula is NOT on row 15 so when you delete a row everything shifts up and the cell that was referring to that deleted cell no longer has a cell to point to. The best thing would be to have the cell refer to cells on the same row and delete them together. Another possible solution is to use dynamic arrays (if you have excel 365) but the behavior will be different as I will show below. So in this image I did that calculation 2 ways:
so in A was just a sequence of numbers 1 to 50
in B1 is the formula you noted: =A15+0.01 and then filled down
in C1 is an array formula version =A15:A50+0.01 (no fill down needed as it will 'spill' the results)
after I deleted row 20 (notice how column A now goes 19 then 21) then B6 results in an error because it was looking at row 20 but it no longer exists. C6 does NOT have this problem because the array formula is only in B1 and "spills down" so after the deletion B6 is based on the new value in row 20 so B5 to B6 goes 19.01 to 21.01
so that is great but notice rows 7:19 do NOT agree between columns B and C
Column B will continue to refer to the original cell it was 'linked' to (i.e. B7 is still based on A21) but Column C are all based on the new values in those relative places.
since you gave very little details it is unclear what you are doing (deleting whole row or just a cell and shift up or something else) and it is unclear what outcome would be best or satisfactory. Often just 'Not getting and error' is NOT the right answer.
- nunoterrinfraCopper Contributori got it mate! thank you alot
- nunoterrinfraCopper ContributorIt shows me an formula error