Formulas update on binded grids after insertrows

VCL TMS VCL UI Pack
1

Hi, i need to know if AdvSpreadGrid can automaticaly update formulas on binded grids after InsertRows on one of grids that have binded cell formulas to it?

I have AdvSpreadGrid1(Sheet1) and AdvSpreadGrid2(Sheet2) binded together,
on AdvSpreadGrid1.Cells[1,1] is formula: "=Sheet2!A4",
on AdvSpreadGrid2.Cells[1,1]:='1';
     AdvSpreadGrid2.Cells[1,2]:='2';
     AdvSpreadGrid2.Cells[1,3]:='3';
     AdvSpreadGrid2.Cells[1,4]:='=sum(A1:A3)';

and now I manually triggers row insert: AdvSpreadGrid2.InsertRows(2, 1);

Now AdvSpreadGrid2.Cells[1,5] have value '=sum(A1:A4)', but AdvSpreadGrid1.Cells[1,1] is incorrect and has the same value as before "=Sheet2!A4", where the correct value should be "=Sheet2!A5" after row insert.

How could I make it work as desired or what i did wrong?

Thanks for any help,
  Grzegorz.

2

Please call

AdvSpreadGrid.Recalc;  after you did the insert of the new row.
3

No, I need to recalc binded grids, not the one that has row inserted.
Is there a way to do this?

4

Did you try to loop through GridBinder.Grids and call grid.Recalc for each of the grids bound?

5

Yes, I did. Recalc function does nothing to formulas that point to wrong cell adresses after InsertRows on binded grids. If that adress for Grid1 was let say pointing to Sheet2!A4 for Grid2, and after Grid2.InsertRows(2, 1); Grid1 is still pointing to Sheet2!A4 - which is wrong - and it should point to Sheet2!A5.

Why this is not automatic? After Grid2.InsertRows(2, 1); only Grid2 is recalculated and not the binded grids!!!

What can I do to have the same functionality as it is in Excel?

6

At this point, automatic formula updating after row insert / delete operations is only done for the grid internally where the row insert / delete happens and not automatically for the bound grids. This is currently a limitation of grid binding.

7

Ok, I know that. And I did my own solution to this problem. If You want the code, please contact me at my private e-mail address: gmnevton(a)gmail(dot)com

8
Let me question by taking advantage, please.
I use TMS Component pack 8.8.0.0.

Even if I go on AdvSpreadGrid1.ReCalc when not InsertRows the question contents, 
it isn't recalculated.
I think that AdvSpreadGrid1 is to finish processing around 5 line of the ReCalc method 
because there are no references to itself.

------------------------
  UpdCellRef;  <-- It is updating the AdvSpreadGrid2.FCellRefList.

  // there are no formulas, so skip recalculation
  if FCellRefList.Count = 0 then  <-- It is true.
    Exit;        <-- Exit the ReCalc procedure. So not recaluculated!!
------------------------

Is this limitation of grid binding ?

9

At this moment, it is a limitation that formula cell reference updating in response to insert or remove rows is handled only in the grid where the insert or remove happens.

10
My question is not the case when inserting or deleting the line.
AdvSpreadGrid1.Recalc method do not execute the formula in a case only of a reference to Sheet2.
I think that this is problem, is not limitation.

AdvSpreadGrid1.Cells[1,1] is formula: "=Sheet2!A4"
Other Cells in AdvSpreadGrid1 is empty.

AdvSpreadGrid2.Cells[1,1]:='1'; 
AdvSpreadGrid2.Cells[1,2]:='2'; 
AdvSpreadGrid2.Cells[1,3]:='3'; 
AdvSpreadGrid2.Cells[1,4]:='=sum(A1:A3)';
AdvSpreadGrid2 do not execute row insert/delete.

AdvSpreadGrid1.ReCalc do not execute. The reason is below.
------------------------
  UpdCellRef;  <-- It is updating the AdvSpreadGrid2.FCellRefList.
                   It is not updating the AdvSpreadGrid1.FCellRefList.

  // there are no formulas, so skip recalculation
  if FCellRefList.Count = 0 then  <-- It is true because this is AdvSpreadGrid1.FCellRefList.
    Exit;        <-- Exit the ReCalc procedure. So the formula "=Sheet2!A4" is not recaluculated!!
------------------------

11

To TMS developers, please make Your components usable, You are taking money for this, try to deliver quality stuff, not just talk about limitations. In my opinion "the sky is the limit", so please, do something about this.

12

The history of this support thread made it confusing.
We have now seen the last mentioned issue and applied a fix for it that will be included in the next release.

13

I'm a registered user.
Is money needed separately to question in this forum?

Registered my Email is kitahata%wingcomputer.com
(The % is for SPAM. Please replace the % to @.)
14

There is NO money needed. As a registered user you get this update FREE when it is released.

15

I'm sorry to make them be confused.

I'll question by a new thread from the next.