can anyone tell me how to have Excel behave as I expect and calculate things correctly when part of a row is deleted?
What I am referring to is having a number of columns of changing data (A - H) and then calculation columns to the right of the data (I - V). All of the calculation columns have data right down for maybe 30,000 rows and are designed so they are blank until column H has data. New entries from A - H are added daily and the calculations just occur correctly
The problem arises when part of a row in the data columns needs to be deleted. I am only deleting the row of data columns (A - H) and the calculation columns are untouched. Surely Excel is savvy enough that if the data in, let's say, row 100 from A - H is deleted and A - H in the rows below it are shifted up, that the calculation columns to the right still calculate whatever is in the columns to the left. It is maddening to constantly be faced with cells showing #REF! in them and when you look at the row in question, all the calculation cells now have #ref! in their formulas instead of the correct cell reference which they had previously. I never touched the calculation columns, so why can they not simply calculate the same cells as the original formula had?
Surely if A100 to H100 are deleted and A101 to H101 and all below, move up to replace them, that they now simply become the new A100 to H100 and so on. They can't possible move up and keep their row as 101. If they do not, then there appears to be a sever limitation being exhibited.
Is there any way to alleviate this, as the sheet will have these sorts of deletions constantly, yet it is needed that sheet calculates correctly?
I mean, rather than deleting the row, would I be best to simply highlight the data in A - H and hit delete, deleting the content and not the row and maybe have a macro which could adjust things to remove the blank row and repair any issues and get it back to calculating correctly?
Any help gladly accepted, as it is frustrating for such a high-tech app to have such a basic limitation
What I am referring to is having a number of columns of changing data (A - H) and then calculation columns to the right of the data (I - V). All of the calculation columns have data right down for maybe 30,000 rows and are designed so they are blank until column H has data. New entries from A - H are added daily and the calculations just occur correctly
The problem arises when part of a row in the data columns needs to be deleted. I am only deleting the row of data columns (A - H) and the calculation columns are untouched. Surely Excel is savvy enough that if the data in, let's say, row 100 from A - H is deleted and A - H in the rows below it are shifted up, that the calculation columns to the right still calculate whatever is in the columns to the left. It is maddening to constantly be faced with cells showing #REF! in them and when you look at the row in question, all the calculation cells now have #ref! in their formulas instead of the correct cell reference which they had previously. I never touched the calculation columns, so why can they not simply calculate the same cells as the original formula had?
Surely if A100 to H100 are deleted and A101 to H101 and all below, move up to replace them, that they now simply become the new A100 to H100 and so on. They can't possible move up and keep their row as 101. If they do not, then there appears to be a sever limitation being exhibited.
Is there any way to alleviate this, as the sheet will have these sorts of deletions constantly, yet it is needed that sheet calculates correctly?
I mean, rather than deleting the row, would I be best to simply highlight the data in A - H and hit delete, deleting the content and not the row and maybe have a macro which could adjust things to remove the blank row and repair any issues and get it back to calculating correctly?
Any help gladly accepted, as it is frustrating for such a high-tech app to have such a basic limitation
