How Using Reference Style in Formulas

Posted by: robertobonilla2007 on 8 September 2017, 2:07 pm EST

  • Posted 8 September 2017, 2:07 pm EST

    I try to use a formula in the sheet2 of my work book but i receive error when use the code below

      
                    FpSpread1.ActiveSheet.ReferenceStyle = Model.ReferenceStyle.A1
                    Dim formula As String = "SUM(Sheet1!B1:Sheet1:B10)"
                    FpSpread1.Sheets(2).Cells(10, 2).Formula = formula

    but receive the next error

     Invalid token was encountered. Error offset: 4

    I Don't know why ?

    Somebody can help me

    Thanks in advance

    RBCrying

     

     

     

     

     

     

  • Replied 8 September 2017, 2:07 pm EST

    Well I try with this code but always receive error message

                    Dim _Range As String = "Sheet1!B1:B10"

                    FpSpread1.ActiveSheetIndex = 1
                    FpSpread1.Sheets(0).ReferenceStyle = Model.ReferenceStyle.A1
                    Dim formula As String = "AVERAGE(" & _Range & ")"
                    FpSpread1.Sheets(1).Cells(5,5).Formula = formula

    Could You Tell me what it's wrong in this code, because i check the name of the sheet and is correct Sheet1, the range also contain values I try following the help example

     

    Another example would be keeping a running total off cells of one sheet on a
    separate sheet. Use the Formula property to put a formula on one sheet that
    references the cells you want added from another sheet.


        FpSpread1.Sheets(1).Cells(0,0).Formula = 
            "SUM(Sheet1!A1:Sheet1:A100)"


    and use the ReferenceStyle property to set the reference style.

    But Does Work!

    Some body can show me the correct way to do that?  CryingIndifferentTongue Tied

    RB

     

     

     

     

  • Replied 8 September 2017, 2:07 pm EST

    Hello,


    I tested your code with the FpSpread version 4.0.2012.2005 with values in Sheet1 (i.e.FpSpread1.Sheets(0).Cells(0, 1).Value = 1) and its working fine at my end.



            Dim _Range As String = "Sheet1!B1:B3"
            FpSpread1.Sheets(0).Cells(0, 1).Value = 1
            FpSpread1.Sheets(0).Cells(1, 1).Value = 2
            FpSpread1.Sheets(0).Cells(2, 1).Value = 3


            FpSpread1.ActiveSheetIndex = 1
            FpSpread1.Sheets(1).ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
            Dim formula As String = "AVERAGE(" & _Range & ")"
            FpSpread1.Sheets(1).Cells(1, 3).Formula = formula


    Can you please let us know what is the error message that you are getting now and the version of the FpSpread you are using?


    Thanks,

  • Replied 8 September 2017, 2:07 pm EST

    I Using FpSpread version 4.0.2011.2005

    Maybe that is the problem?

     

    RBCrying

  • Replied 8 September 2017, 2:07 pm EST

    Hello,


    In cross-sheet referencing of formulas, to calculate the sum of range of cells in sheet-1 and putting the result in other sheet we set the formula as:



    FpSpread1.ActiveSheet.ReferenceStyle = Model.ReferenceStyle.A1
    Dim formula As String = "SUM(Sheet1!B1:B10)"
    FpSpread1.Sheets(2).Cells(10, 2).Formula = formula


    Hope this will help you.


    Thanks,

  • Replied 8 September 2017, 2:07 pm EST

     Reeva, thanks for you anwers

    I follow you code but i receive the next error message Invalid token was encountered. Error offset: 4

     

                    FpSpread1.ActiveSheet.ReferenceStyle = Model.ReferenceStyle.A1
                    Dim formula As String = "SUM(Sheet1!B1:B10)"
                    FpSpread1.Sheets(2).Cells(1, 30).Formula = formula

    Could you tell me what it's wrong ??

    Please Let me know, Regards

    IndifferentCryingTongue Tied
    RB

  • Replied 8 September 2017, 2:07 pm EST

    Roberto,


    The error message "Invalid token was encountered. Error offset: 4" indicates that there is a problem starting at character index 4 of the string representation of the formula.


    In your first post, the token starting at index 4 was "Sheet1!B1:Sheet1!B10".  Spread supports the ":" character as part of a range reference but not as a range operator.  Thus, "Sheet1!B1:B10" is valid in Spread, but "Sheet1!B1:Sheet1!B10" is not valid in Spread.  Note that Excel supports the ":" character both as part of a range reference and as a range operator.  Thus, both "Sheet1!B1:B10" and "Sheet1!B1:Sheet1!B10" are valid in Excel.


    In your second post, the token starting at index 4 was "Sheet1!B1:B10".  Make sure that your spreadsheet contains a sheet named "Sheet1".  By default, the first sheet is named "Sheet1".  However, sheet names can be changed using the SheetName property (e.g. fpSpread1.Sheets[0].SheetName = "Alpha").  Make sure that cross sheet references are enabled.  By default, cross sheet references are enabled.  However, cross sheet references can be disabled using the EnableCrossSheetReference property (e.g. fpSpread1.Sheets[0].CrossSheetReference = false).

  • Replied 8 September 2017, 2:07 pm EST

    Hello


    It is working fine at my end with version 4.0.2011.2005 as well. Can you please post a small zipped project for us to debug?


    Thanks,

  • Replied 8 September 2017, 2:07 pm EST

    I Can Explaint what was happening, but  finally did the project again and it works

    Thanks All for Your Help

    Regards

    RBBig SmileWinkParty!!!

     

     

Need extra support?

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

Learn More

Forum Channels