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

    RB<img src="/emoticons/emotion-9.gif" alt="Crying" />

     

     

     

     

     

     

  • 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:


    <BLOCKQUOTE>

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

    </BLOCKQUOTE>

    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 <font color="#cc0033"><i><b>Invalid token was encountered. Error offset: 4 </b></i></font>

     

                   <font color="#ff0000"><b> FpSpread1.ActiveSheet.ReferenceStyle = Model.ReferenceStyle.A1
                    Dim formula As String = "SUM(Sheet1!B1:B10)"
                    FpSpread1.Sheets(2).Cells(1, 30).Formula = formula</b></font>

    Could you tell me what it's wrong ??

    Please Let me know, Regards

    <img src="/emoticons/emotion-8.gif" alt="Indifferent" /><img src="/emoticons/emotion-9.gif" alt="Crying" /><img src="/emoticons/emotion-7.gif" alt="Tongue Tied" />
    RB

  • Replied 8 September 2017, 2:07 pm EST

    Well I try with this code but always receive error message

                   <font color="#3300ff"><b> Dim _Range As String = "Sheet1!B1:B10"</b></font>

    <font color="#3300ff"><b>                FpSpread1.ActiveSheetIndex = 1
                    FpSpread1.Sheets(0).ReferenceStyle = Model.ReferenceStyle.A1
                    Dim formula As String = "AVERAGE(" & _Range & ")"
                    FpSpread1.Sheets(1).Cells(5,5).Formula = formula</b></font>

    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.


       <font color="#ff0000"><b> FpSpread1.Sheets(1).Cells(0,0).Formula = 
            "SUM(Sheet1!A1:Sheet1:A100)" </b></font>


    and use the ReferenceStyle property to set the reference style.

    But Does Work!

    Some body can show me the correct way to do that?  <img src="/emoticons/emotion-9.gif" alt="Crying" /><img src="/emoticons/emotion-8.gif" alt="Indifferent" /><img src="/emoticons/emotion-7.gif" alt="Tongue 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.


    <BLOCKQUOTE>

            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

    </BLOCKQUOTE>

    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 <font color="#ff0033"><b>4.0.2011.2005 </b></font>

    Maybe that is the problem?

     

    RB<img src="/emoticons/emotion-9.gif" alt="Crying" />

  • Replied 8 September 2017, 2:07 pm EST

    Hello


    It is working fine at my end with version <FONT color=#000000>4.0.2011.2005 as well. Can you please post a small zipped project for us to debug?</FONT>


    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

    RB<img src="/emoticons/emotion-2.gif" alt="Big Smile" /><img src="/emoticons/emotion-5.gif" alt="Wink" /><img src="/emoticons/emotion-19.gif" alt="Party!!!" />

     

     

  • 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).

Need extra support?

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

Learn More

Forum Channels