Handsontable formula #VALUE! error even the formula for the cell is correct

user_6555126 0
Viewed 498

Handsontable is throwing #VALUE error even the formula is correct.

To understand my problem please replace the var data1 from this example http://jsfiddle.net/qfpfxgw5/ with the following data.

var data1 =
[
["","","","","","","","","",""],
["","","","","","","","","",""],
["","","","","","","","","",""],
["","","","","","","","","",""],
["","","","","","","","","",""],
["","","","","","","","","",""],
["","","","","","","","","",""],
["","","","","","","","","",""],
["","","=C13-C14","=D13-D14","=E13-E14","=F13-F14","=G13-G14","=H13-H14","=I13-I14","=SUM(C9:I9)"],
["","","985149",21651,35565,985149,548,312495,35195,"=SUM(C10:I10)"],
["","",3563546,35635,35635,75345,54245,723445,53577,"=SUM(C11:I11)"],
["","",0,0,35565,0,0,312495,0,"=SUM(C12:I12)"],
["","",3563546,35635,"=D13+E10+E12","=E13+F10+F12","=G12+G10+F13","=H12+H10+G13","=I12+I10+H13","=SUM(C13:I13)"],
["","",3563546,35635,"=D14+E11","=E14+F11","=F14+G11","=G14+H11","=H14+I11","=SUM(C14:I14)"],
["","",50,50,50,50,50,50,50,"=SUM(C15:I15)"],
["","",3550,3550,3621,4800,3550,3550,3300,"=SUM(C16:I16)"],
["","",8,8,8,8,8,8,8,"=SUM(C17:I17)"]
];

And run.

You will see #VALUE! error on E9,F9 and so on.

We have

E9=E13-E14;  
E13=D13+E10+E12;  and
E14=D14+E11

why doesn't it give the expected output until i again reset the value of E9=E13-E14. What should be the another solution to solve it ? Thanks in advance.

handsontable excel
1 Answers

I assume because those cells also have formulas attached to them. So for some reason if doesn't render becuase of this.

Solution?, not sure, I tried getting a cell with a formula from other cells further up to see if maybe once they rendered, the cells below would work, but it didnt.

Edit: I'm actually looking into something similar and came up with a not so pretty solution:

http://jsfiddle.net/qfpfxgw5/2/

Basically if you are referencing a value from a cell which also has a function associated with it, it wont work.

So, to make it work, you have to include the actual formula from that other cell within the formula for your current cell.

example:

cellA13: = E13-E14
cellE13: = SUM(B2,B3)
cellE14: = 100

This doesnt work, but if you change it to this:

cellA13: =SUM((SUM(B2,B3))-E14)
cellE13: =SUM(B2,B3)
cellE14: =100

My example above does something similar and it worked.

Ugly... but i dont know how else to make it work.

user_8234220
Related