Paste formula for multiple cells

Posted by: suresh-m on 8 September 2017, 2:14 pm EST

  • Posted 8 September 2017, 2:14 pm EST

    Hi,

    I have to implement paste functionality like as Excel paste special.  I have copied one Cell from spread, Then I have to paste the copied cell formula to more cells. currently supporting to paste formula to one cell. I need to paste the relevant formula for all the selected cells.

    In Excel, If we copy one cell, the paste special, support to "Paste Formulas" for selected more cells.

    We can select more cells then paste formula for selected all the cells. It will arrange and paste the formula for all the cells  like copied fornula but not the same. Kindly let me know, Is there any sample code to achieve this functionality.

      Thanks in Advance

    Best Regards,

    Suresh

     

  • Replied 8 September 2017, 2:14 pm EST

    Hello Suresh,


    Once you paste the value into a cell you could use AllowDragFill property of Spread which determines whether the user can fill a selected range of cells by dragging the cells after copying the contents of one cell.


    When this property is set to true, the user can fill the contents of a cell or a range of cells using the drag-and-drop operation. And to allow dragging of formulas you can easily catch the DragFillBlock event and setting the DataOnly parameter to False.


    Code for the same is given below:



        fpSpread1.AllowDragFill = true;
            private void fpSpread1_DragFillBlock(object sender, FarPoint.Win.Spread.DragFillBlockEventArgs e)
            {
                e.DataOnly = false;
            }


    Hope this will help you. Thanks.

  • Replied 8 September 2017, 2:14 pm EST

    Don't know how this works. I set the code above. In a cell I used sum formula with cells H1, l1. It got displayed as =SUM(H1:l1).

    Then I copied the cell and pasted in cell X1, the formula shown over there is =SUM(Y1:Z1) and the result is 0. Is it possible to retain the same formula as

    =SUM(H1:l1), in cell X1 ?

  • Replied 8 September 2017, 2:14 pm EST

    Hello,

    I would like you to know that in Spread, the cell references passed to the formulas are of two types, absolute reference and relative reference. When absolute cell references are passed, the formula remains same and accesses the same cells every time, to calculate a result. However, the relative cell reference passed to a formula, makes the formula to refer a different cell range relative to the location where the formula is placed. The cell references passed with a $ symbol means that we are passing absolute cell references, where as the one passed without this symbol refers to relative cell references. For example, if cell C2  has the following formula =SUM(A1:B1), then it means it is given a relative reference, and it would always refer to two cells in the row above it, like A1 and B1 in this case, now if this formula is pasted to cell E2, then it would change to =SUM(C1:D1), referring to two cells in the above row i.e C1 and D1.

    Now if you don't want this formula to vary with the cell location then you would need to pass absolute cell references to this formula using the following syntax:

                             =SUM($A$1:$B$1)

    So, whenever you would paste the formula, it would remain the same and would always refer to cells A1 and B1. You can also refer to this link for further clarification.

                          http://www.clubfarpoint.com/FarPointSupportSite/Modules/Docs/FormulaReference/formulas-relabsolute.html

    Hope it will help you. Please let me know if you have any queries further.

    Thanks,

    Manpreet Kaur.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels