Posted 8 September 2017, 3:52 am EST
I have an Excel like process to display the Sum, Average, or Standarad Deviation of any selected block of cells.
I use the SelectionChanged event to get the currently selected block and then use the Column and Row information from that CellRange (after converting '-1' values [all rows/column] to real row/col numbers) to construct a formula.
(I have the spreadsheet set to use R1C1 type notation for ease of use, and I temporarily use a cell in the 'next' [unused] row to store the formula and perform the calculation.)
This works great ... until I sort the spreadsheet.
After sorting the spreadsheet the Column/Row numbers returned by GetSelection still match what I see on the screen - but apparently they dont match the rows that the formula acts on because I get completely incorrect results.
Can anyone tell me what I need to change to make this work correctly?