Unfinished Business WIth (Cell)ReferenceHelper After Columns/Rows Deleted

[oleibman]

After a very long delay, PR #3528 has been moved to ready status, and will be installed soon, warts and all. And it has warts aplenty. It fixes some problem, and doesn't break anything. But it leaves some outstanding problems. In the hope that dealing with those will be easier if they are addressed in bite-sized pieces, I am opening this ticket to document what is missing.

• Unit tests for data validations.
• Unit tests for comments.
• Unit tests for defined names.
• Other unit tests.
• Performance. See (closed but with ongoing problems) issue insertNewRowBefore is more and more slow for large files #2509.
• Formulas need to be adjusted on other sheets if they refer to cells affected by cell/column/row adds/deletes. See issue insertNewRowBefore and/or removeRow, does not update cell Reference in formula on other sheets #3380. Note that any resolution to this will probably exacerbate performance problems.
• When the left/top part of a range is deleted and formulas refer to the range, Excel will often reevaluate the formula as #REF!.
• See also issue removeRow with merged Cells apply the same merging to the row below #4077.