Sorting confuses future formulas

Sorting confuses future formulas

Posted by: mikedempsey on 8 September 2017, 3:52 am EST

• 

  mikedempsey

  • Post Options:
  • Link
    Copy

  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?

  Thanks

  Mike

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:52 am EST

  Mike,

  There are two things you can do to rectify this issue.

  1) Get the model coordinates of the row before sending it to the Formula. When you get the selection, you can use the view indices passed in by this method and call the GetModelRowFromViewRow method on the Spread to determine the model indices for use in the Formula.

  2) When sorting the Spread, if you sort the DataModel instead of just the view, it would be a slower sort, but you would not need to translate coordinates after the sort. You can programatically call the SortRange method for this sort to be implemented.

• 

  mikedempsey

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:52 am EST

  I understand the idea of sorting the datamodel - but as you say that is slower so I wanted to avoid it. (may be other issues too. I havn't looked into how to do it, or what else it might effect in my current app. Note that this is for a database app where there might be 100s of thousands of rows.)

  I dont understand how the other method would work though.
  
  If I get the original (model) row numbers for the first and last row in the user's selected (view) range. That will guarantee that it works only if they chose a range of 2 rows.
  
  It is highly unlikely that the rows currently between the current first and last selected rows are the same ones that were originally between these 2 rows.

  For example if my original rows were 0,1,2,3,4,5
  
  and my new rows are 0,2,4,1,3,5
  
  and the user selects 'view' rows 1 to 3 (original rows 2, 4 and 1)
  
  Converting 1 to 3 into 'original' row numbers I would get a range from 2 to 1 - in other words it would sum only those 2 rows instead of 1,2 and 4.

  This only seems to be a problem with rows however. If I rearrange the columns it still summs the correct values.
  
  
  
  Mike 

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:52 am EST

  Mike,

  You would need to get each row in the selection index into the DataModel and add each cell into the formula. You would not be able to provide a range into the formula.

• 

  mikedempsey

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:52 am EST

  So basically you are saying that (row reference) formulas are useless once a spreadsheet has been sorted, because if I have to spin through every row anyway it would be faster to simply add the contents of the cells as I go rather than create a formula that might reference thousands of individual rows.

  I guess my only option is to provide the user with a choice of whether they want to physically sort the model (and get valid totals after sorting) or get a fast sort with no totals available after the sort.

  This means that even if a USER enters a (row range) formula after sorting the rows then the displayed result will be complete garbage.
  
  (Doesn't apply in my case since the spreadsheet is ReadOnly but others might like to know this. I definately didnt see any (Bold) warnings about that in the documentation describing Sort.)

  Mike  

• 

  bobbyo

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:52 am EST

  Mike,

  The spreadsheet is divided into a model (which stores data and formulas) and a view (which displays the data).  The view and model initially use the same coordinates.  After a view based sort, the view and model coordinates can be different.  It is even possible to have multiple views of the same model where each view has a different visual ordering.  Since formulas are stored in the model, formula references are stored in model coordinates.  Since continous ranges in the model may or may not be continous ranges in the view, there is no way to convert the stored formula into a text resprentation using view coordinates.  Thus, the text representation of formulas is always in model coordinates.

  Formulas work perfectly fine with view based sorting, but the differences in coordinate systems would be very confusing to most end users.  Thus, if you want the end user to both sort rows and edit/view formulas then you probably want to use model based sorting.

• 

  mikedempsey

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:52 am EST

  OK. I can add an option that allows users to request 'slower' sorting in order to still be able to use formulas. (I have already added code so that I can block attempts after a regular sort has been performed - to avoid incorrect data)

  But I have not been able to find a way to tell it to sort the model data - after the fact - as it were. If the user uses AutoSort the docs say that is always a sort of the View only. I see there is a method to reset the view row order to match the model order - basically an 'unsort' method, but I can not find a method that does the opposite - reorder the Model to match the current view.

  Unless there is such a method the only way I can see to force sorting of the model (unless I missed a boolean flag somewhere that effects all sort commands)  is to

  1) Always use SortRange() instread of SortRows() when I do the sorting
  
  and
  
  2) Intercept the users attempts to AutoSort and
  
      a) Cancel the AutoSort
  
      b) Call SortRange() to do a kind of 'fake' AutoSort

  This seems a rather long winded process just to be able to use formulas after sorting. (And I'm not sure I even have all the details correct.)

  Mike

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 3:52 am EST

  Mike,

  What you said is correct. There is not a method to rearrange model coordinates after a sort to get them to match the view coordinates, without changing the view coordinates also. It would be best to catch the AutoSortingColumn event and cancel it so you can programatically implement your own sort.

• 

  hoang.nguyen

  • Post Options:
  • Link
    Copy

  Posted 14 September 2022, 9:38 am EST

  can you please create a fix for this? the formula still works when you filter data. thanks

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 14 September 2022, 6:18 pm EST

  Hi,

  Could you please provide some details about the issue? if possible, please share a sample replicating the issue so we can investigate the issue at our end.

  Regards
  
  Avnish