Why Does Drag Cell Not Copy Sum (and translate cell refs)?

Tags: #<Tag:0x00007efc64f5da90>
#1

Hi Chaps,

Having a problem with a pro installation. On the example at…

http://handsontable.github.io/handsontable-ruleJS/

If you drag and drop a cell one place to the right with =SUM(A1:B2) then you get =SUM(B1:C1). This is good and is what we want.

However on our deployed version this does not work. To recrecreate…

  1. Go To http://dev.financialmodelr.com/signin
  2. Sign in with test@test.com and M0delrapp
  3. Go to http://dev.financialmodelr.com/model/161
  4. Drag and Drop G8 --> G9

Expected : the ‘sum’ will be copied and will update the relative cell refs
Actual : the ‘value’ is copied instead

I have set formulas:true.

Please can someone advise? This is a critical aspect.

Thanks,
Pete

#2

Hi @pete

I do not have good news. PRO won’t work with RuleJS as PRO has been added to version 0.20.3 of Handsontable and RuleJS is updated to version 0.14.1.

However, RuleJS should do the same calculations as Formula Plugin which is a part of Handsontable PRO.

Currently, RuleJS and Formula Support plugins do not support drag and drop of the formula. And the only workaround for it is to use the beforeAutoFill hook to replace values for the formula strng.

#3

Hi,

This is confusing. The documentation says you support relative references.

What is the point in supporting relative references if you don’t support drag and drop? You might as well use absolute references for everything.

How difficult will it be to implement this functionality?

I have to be able to translate row/column refs either horizontally.

Also you look like you have this on the backlog…

If it isn’t here my questions are -

  1. when will it be implemented?
  2. do you have any examples online of manually making these changes?
#4

Also I have tried your suggestion using beforeAutofill and it does not give me the location of the source cell so there is no way to get hold of the original formula. Please advise.

#5

Yes, we will be adding the changes in 2018 accordingly to what you have found on our Trello. I think that the list will just be copied to Trello for 2018.

And I can agree that without populating the formulas it’s hard to call it a complete plugin. That is why it’s still alpha.

When it comes to examples it’s not that easy as it all depends on the formula. Let’s say we have an example like this http://jsfiddle.net/rqe4nd6v/

and we define

'=IF(B1<SAMPLE, "yes", "no")'

for the cells in column C.

In this case, it’s pretty easy. We just need to change B1 to B2 and then to B3. As you rightly spotted we should just use the $B1 in the first cell on the C column and that’s it. However, as it hasn’t been implemented yet (can call it a bug as well) we would need to iterate.

ps. the beforeAutofill example

#6

Hi your example doesn’t make it clear how I can set the autofill to force the cell to be a new value?

How can user can copy the cell formula not the value by drag and drop?
#7

Sorry, but I did test some approaches and even when we get the right formula for a cell it stays as a string - it doesn’t recalculate.

#8

Hey @pete, I just finished implementing formula drag myself. I used the modifyAutofillRange hook which includes the corners of the source of the drag and the afterAutofillInsidePopulate hook which is called for each cell being updated and includes the direction of the drag to be able to get enough information to calculate the new cell references. You can manually call a recalculate when you finish to get the formulas to convert to values. Hope that helps :slight_smile:

#9