Hi,
I am using predefined formulas from the backend in my data that is being loaded into my table. Example is "=SUM(R39/F39)G39". the 39 is the index of the data row as provided by the backend. While doing filtering and sorting some cells will return #REF and if i check the formula it shows: =SUM(#REF/#REF)#REF. However the issue is intermittent. sometimes #REF do not show at all. May I know what I’m doing wrong? Do I need to reset the formulas based on the visual indexes?
Thanks,
VJ
Hi @vanjoe
the #REF! error is shown when the following cell does not exist. But it may also happen when rows are reordered like in this case [GH #1975] Sorting issue with HandsonTable when using the compareFunctionFactory function - #6 by adrian.szymanski.
While it is correct (specified by Excel as well) to show #REF! error for rows that are not there, row ordering should be fixed (this is already reported as a bug).
Now within the filters rows are not removed and the reference should be accessible. This can be tested here handsontable (forked) - StackBlitz.
Example
When I type =A1 in the last cell
then I filter the A1 out
I am still able to see the A1 value in the last cel of the B column
So, there might be a different case in your example. Could you please share a demo where the issue can be replicable?
Thanks for the fast reply. I will try to share a demo for you to check it out. One thing I forgot to say is that the table has 500+ rows and around 30+ columns with the first 4 columns are frozen. Can it be that the size of the table is causing this issue?
I’m also ok with disabling the ordering and filtering with the columns containing the formulas but the problem is still there. What confuses me is that the issue is intermittent.
I do not think so.
Also it the linked issue it was about reordering rows in general. It’s quite different from what filers are doing so let’s wait for the demo.
I think I have to confirm that its the reordering thats causing the issue. If I reorder the column with the formula the values are not correct anymore. I guess I will have to disable ordering on these columns.
Please tell me how the data is reordered. In the issue (queued to be done) we used columnSorting plugin. If you use a different method I would still ask for a demo and steps to replicate the issue as our fix might not be suitable for this use case.
Hello, here is a link to a demo - https://codesandbox.io/p/devbox/jf8d5g. I also use the columnSorting plugin - I don’t use any custom sorting.
Could you please tell me what are the steps to replicate the issue? I tried sorting by one or multiple columns and sorting + filtering and could not replicate the issue.
Hello,
I refer to the screenshots below. This table is running on my dev machine so you wont see this data on the demo I shared.
Here I have filtered the list to show one buyer only. The values in green on the left side are calculated with formulas and they show correct values.
Now I cleared the filter and resorted the buyer column and applied the same filer again. As you can see the left green columns show 0 which are wrong.
Finally I have cleared the filter but kept the sorting. The values are different again.
This is what puzzles me. The demo has around 131 rows and my list I am showing in the screenshots has 500+ rows.
Thanks,
VJC
