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
This steps is the one I’m at. You mentioned
But there is no 0 in your screenshot nor in my demo. Could you tell me how to replicate the last step using the demo that you sent?
Hi, I have added more data to the demo. You should be able to filter the buyer column to “Jukraf…” and see what I’m experiencing. Hope you will have some idea of whats going on and how to solve this issue.
Thank you. I will review the demo and be back (hopefully) before the weekend as I am on a training for 3 days.
Thank you. I was able to replicate the issue.
One question. Please tell me if the formula is correct? We are at the row number 20 but the formula is passed for values in the row 62.
Hi, thats what I mean. when i sort or filter the formula for some reason changes. i thought that row# would change automatically. I preset the formulas in the data being loaded. Or is that the wrong way?
Hi Andrea,
I refer to this screenshot which shows the correct row# for the “jukraf” record
As you can I have not applied any sorting or filter on the Buyer column.
Now here is the same screenshot after I sorted the Buyer column.
Am I doing something wrong when loading the data? Should formulas not be defined in the data when loaded?
Hi Andrea,
One thing I neglected to tell you is that this table is in a tab item on a page where there are multiple tabs, each tab having a sheet ( as component). Can it be this reason?
I see the =ROUND(S62*P62,2) formula for Buyer Jukraf Furniture Sdn Bhd without sorting - on the initialization of the table.
As I can see there is no predefined sorting applied within https://codesandbox.io/p/devbox/jf8d5g?file=%2Fsrc%2Fcomponents%2FStockMasterList.vue%3A1210%2C20-1210%2C24 demo so it makes me wonder if the dataset it correct.
Shouldn’t it be =ROUND(S20*P20,2) for Buyer Jukraf Furniture Sdn Bhd ?
The correct row is 440 from the data predefined coming from the server.
This is the data (you can also see it in the sandbox)
Should I predefine a sorting column? right now there is no column which is default sorted.
Could you please simplify the code so we only have the columns that are needed, no other API? I wasn’t able to replicate similar issues using our documentation so there can be a mismatch between the options. Using the simplest example would allow us to debug the code easier and exclude some of the ideas.