Cross Sheet Formula not working

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

  • Posted 8 September 2017, 12:44 pm EST

    I have a formula "IF('Minimum Take'!B1<0,0,'Minimum Take'!B1)"


    and this celltype is a NumberCellType, but the .Value is a string,


    So my formula doesn't recognize negative values. What am I doing wrong?And this is a cross sheet formula, if it matters.


    I'm using FarPointSpread 2.5.2009.2005 for Windows. Thanks in advance,


    John

  • Replied 8 September 2017, 12:44 pm EST

    John,


    The problem is not in the cross sheet referencing of the formula. The problem is the value in the cell is a string. The string value will never be less than 0, so you will get the string value in the formula cell no matter what you type. You need to have the Value property hold the numeric value of the string and not the string itself.

  • Replied 8 September 2017, 12:44 pm EST

    Scotts,


    Thanks for the speedy reply!


    This cell is set by the fpSpread control using a formula. How can I force this value to be a integer instead of a string?


    It appears as though, no matter what the celltype is, any value that is assigned comes out as an integer! How can I stop this action from happening???

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    I do not understand what you are asking. The CellType property has no affect on how the data is stored in the DatModel except where a user types data into the editor, the CellType will Parse the data for the DataModel. If you programatically put data into a cell, the type of the data is defined by you and can be any object type data.


    How is the data being put in the cells as strings? You said a formula is doing this. What formula are you using?

  • Replied 8 September 2017, 12:44 pm EST

    Scotts,


    I have bound my fpspread to a datatable, some columns are null. When is set a particular cell.Value = 0, I get .Value = "0"


     


    fpsMain.Sheets(iSheet).Cells(4, iCol).CellType = Me.m_oDecimalCellType ' Minimum Take Deficiency


    fpsMain.Sheets(iSheet).Cells(4, iCol).Value = 0


    In the Watch window I can see that


    fpsMain.Sheets(iSheet).Cells(4, iCol).Text = "0.00"


    fpsMain.Sheets(iSheet).Cells(4, iCol).Value = "0" {String}


     


    This should be easy to reproduce. However, my original datatable column is probably string, because of the way I had to created it with column headers basically going across the top, and down the first row.


    If I had an email address, i could send you a screen shot, and it would probably make more sense what I am trying to accomplish.


     


    John


     

  • Replied 8 September 2017, 12:44 pm EST

    John,


    There is something else changing the value 0 to a string "0". The Spread control will not change the data you are programatically putting directly in the DataModel. Is there a formula on row 4? If you are bound to a string field, this could change the value (by way of the bound DataSource. If this is the case, you need to change the field type of these columns for this formula to work. Now you can create a CustomFunction that takes string values and convert them to numeric and programatically do the conditional and pass back the result. Have you worked with custom functions before?

  • Replied 8 September 2017, 12:44 pm EST

    Scotts,


    I believe the Custom function is what I will need, as the datatypes, going down a column change from string to int to decimal.


    Can you point me towards some documentation on how to implement this?

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    There are several posts on the forums showing how to create a custom function. Or you can go to this link in the help file for information on setting this up.


    ms-help://FarPoint.Win.Spread.3.0.VS2005/FarPoint.Win.Spread.3.0.VS2005/spwin-formulacustomfunc.html

  • Replied 8 September 2017, 12:44 pm EST

    Scotts,


    When I put this URL in my browser, I get "Page Cannot be displayed"

  • Replied 8 September 2017, 12:44 pm EST

    You could search for (in the on-line help, developer's guide):

    Creating and Using a Custom Function
  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    The link I sent you is for the online help file that gets installed with the product. For the documentation that is on the web, you can use the following link.

Need extra support?

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

Learn More

Forum Channels