Column formula's

Posted by: john-shugart on 8 September 2017, 12:29 pm EST

  • Posted 8 September 2017, 12:29 pm EST

    Is there a way to set the formula for the entire column without having to set it for each individual cell?


    Thanks in advance for your help.

  • Replied 8 September 2017, 12:29 pm EST

    John -

    The Column class does have a Formula property but one thing to take into consideration is that the references for each cell in the column will automatically be adjusted.  For example, given the following scneario...

    FpSpread1.ActiveSheet.Cells(2, 3).Value = 10
    FpSpread1.ActiveSheet.Cells(2, 2).Value = 10
    FpSpread1.ActiveSheet.Columns(4).Formula = "SUM(C3, D3)"

    The first cell in the column will have a formula of Sum(C3,D3).  The second cell would have a formula of Sum(C4,D4) and so on, so the behavior you are expecting may not be what you get.
  • Replied 8 September 2017, 12:29 pm EST

    Bob,


    Thanks for the speedy reply. What I have is one column's value is based off of a calculation from 2 other colunms. And right now, I'm looping over all the rows and setting the formula for the cell in each row, and I'm dealing with thousands of rows. So needless to say, it takes a little longer than I would like to load the sheets.


    Can I accomplish this task in a simplier, cleaner way?


    Thanks again for your help,


    John Shugart

  • Replied 8 September 2017, 12:29 pm EST

    Is the value the same for every cell in the column or is it based on the calculation from cells in the other two columns?  If it's the latter then the way you are doing it is the only way.
  • Replied 8 September 2017, 12:29 pm EST

    Yes Bob,


    It is different for every cell in every row. It would be a useful feature to be able to set the formula at the header level and let the FarPoint Spread expand the formula down the colum, changing the cell number as it goes.


    Do you think this would be useful for others too, or am I a select case?


     


    John

  • Replied 8 September 2017, 12:29 pm EST

    John -

    As I stated in my first reply, it will change it for every cell in the column.  Let's say for the first row in the column you wanted to have the formula sum the values in cells from two other columns....

    FpSpread1.ActiveSheet.Cells(0, 0).Value = 10
    FpSpread1.ActiveSheet.Cells(0, 1).Value = 20
    FpSpread1.ActiveSheet.Cells(1, 0).Value = 40
    FpSpread1.ActiveSheet.Cells(1, 1).Value = 50
    FpSpread1.ActiveSheet.Columns(3).Formula = "SUM(A1, B1)"

    The value in the first cell of the column  would be 30 and the value in the second cell of the column would be 90 because the formula in the second cell would be "Sum(A2, B2)"
  • Replied 8 September 2017, 12:29 pm EST

    Thanks Bob,


     


    I believe this will do what I need.


     


    Thanks again for the speedy reply.


    John Shugart

Need extra support?

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

Learn More

Forum Channels