Why do formulas get corrupted when I sort a spread sheet with a macro? Is this avoidable?
- Show us your macro and
- Show us your data before running your macro and
- Show us what you expect after running the sort and
- Show us what you get from your macro
Otherwise you probably won’t get any satisfying answer to your “Why” question (My 5ct crystal ball gazing: Your macro sorts only one column and not a whole range of columns).
A competing manufacturer’s crystal ball tells that that could be sorting an array formula range. Let’s bet on the winner ball! 
i understand. Unfortunately there isn’t enough space in the response to include the code. I’m using a Sheet event “Content Changed” to trigger the macro. I’m careful to get the cell that changed in the oEvent CellAddress row and column to only permit sort for a change of a specific cell on the sheet. So the sort is done by a range.Sort(sort description). The sort works fine but cells outside of the range sorted contain formulas that use data within the sorted range. The cell references in the formulas become corrupted each time a sort is done. Any suggestions would be appreciated. Thanks.
Unfortunately there isn’t enough space in the response to include the code
Use edit (right of Comments) to edit your question and attach a file or the code.
> The cell references in the formulas become corrupted each time a sort is done.
What does “corrupted” mean in that context (it just could mean: They point to the incorrect values after the sort and unless you include the cells referencing to cells within the sorted range into the sort range, you can’t avoid that)?
Formulas must refer to cells on the same row. Or the formulas should cover the entire range in height. An array formula cannot be included in a sort range.
I improved my understanding of what is happening. I have a range of data. At the top of the sheet above the range are some formulas based on the values inside the range. I use the range.sort() API method in a macro that sorts the range fine but the sort does not check and update the formulas outside of the range. Since this is the behavior of Libre calc I just made a sort order as “formulas correct” so before using the formula display the range.sort() is first resorted to the “formulas correct” order and the formula values are okay. I supposed the range.sort() might check for links but I can understand why it doesn’t. Thanks all for sharing your insights.
Sorting does not change the data. There is no need to update the formulas. Formulas need to be composed to cover the entire range. However, I have already said it…
It is not necessary to pull out individual data from the range with the mouse, but it is necessary to correctly compose the filter criterion which will remain unchanged throughout the range.
I like Mike’s answer but don’t know how to mark it an answer 