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

    <p>I try to use a formula in the sheet2 of my work book but i receive error when use the code below</p><p>   <br>                FpSpread1.ActiveSheet.ReferenceStyle = Model.ReferenceStyle.A1<br>                Dim formula As String = "SUM(Sheet1!B1:Sheet1:B10)"<br>                FpSpread1.Sheets(2).Cells(10, 2).Formula = formula</p><p>but receive the next error</p><p> Invalid token was encountered. Error offset: 4</p><p>I Don't know why ?</p><p>Somebody can help me</p><p>Thanks in advance</p><p>RB<img src="/emoticons/emotion-9.gif" alt="Crying" /></p><p> </p><p> </p><p> </p><p> </p><p> </p><p> </p>
  • Replied 8 September 2017, 2:07 pm EST

    <p>Well I try with this code but always receive error message </p><p>               <font color="#3300ff"><b> Dim _Range As String = "Sheet1!B1:B10"</b></font></p><p><font color="#3300ff"><b>                FpSpread1.ActiveSheetIndex = 1<br>                FpSpread1.Sheets(0).ReferenceStyle = Model.ReferenceStyle.A1<br>                Dim formula As String = "AVERAGE(" & _Range & ")"<br>                FpSpread1.Sheets(1).Cells(5,5).Formula = formula</b></font></p><p>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</p><p> </p><p>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.</p>
    <p>   <font color="#ff0000"><b> FpSpread1.Sheets(1).Cells(0,0).Formula = 
            "SUM(Sheet1!A1:Sheet1:A100)" </b></font></p>
    <p>and use the ReferenceStyle property to set the reference style.</p><p>But Does Work!</p><p>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" /><br></p><p>RB <br></p><p> </p><p> </p><p> </p><p> </p>
  • Replied 8 September 2017, 2:07 pm EST

    <P>Hello,</P>
    <P>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. </P>
    <BLOCKQUOTE>
    <P>        Dim _Range As String = "Sheet1!B1:B3"<BR>        FpSpread1.Sheets(0).Cells(0, 1).Value = 1<BR>        FpSpread1.Sheets(0).Cells(1, 1).Value = 2<BR>        FpSpread1.Sheets(0).Cells(2, 1).Value = 3</P>
    <P>        FpSpread1.ActiveSheetIndex = 1<BR>        FpSpread1.Sheets(1).ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1<BR>        Dim formula As String = "AVERAGE(" & _Range & ")"<BR>        FpSpread1.Sheets(1).Cells(1, 3).Formula = formula</P></BLOCKQUOTE>
    <P>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?</P>
    <P>Thanks,</P>
  • Replied 8 September 2017, 2:07 pm EST

    <p>I Using FpSpread version <font color="#ff0033"><b>4.0.2011.2005 </b></font></p><p>Maybe that is the problem?</p><p> </p><p>RB<img src="/emoticons/emotion-9.gif" alt="Crying" /> <br></p>
  • Replied 8 September 2017, 2:07 pm EST

    <P>Hello,</P>
    <P>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:</P>
    <BLOCKQUOTE>
    <P>FpSpread1.ActiveSheet.ReferenceStyle = Model.ReferenceStyle.A1<BR>Dim formula As String = "SUM(Sheet1!B1:B10)"<BR>FpSpread1.Sheets(2).Cells(10, 2).Formula = formula</P></BLOCKQUOTE>
    <P>Hope this will help you.</P>
    <P>Thanks,</P>
  • Replied 8 September 2017, 2:07 pm EST

    <p> Reeva, thanks for you anwers</p><p>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></p><p> </p><p>               <font color="#ff0000"><b> FpSpread1.ActiveSheet.ReferenceStyle = Model.ReferenceStyle.A1<br>                Dim formula As String = "SUM(Sheet1!B1:B10)"<br>                FpSpread1.Sheets(2).Cells(1, 30).Formula = formula</b></font></p><p>Could you tell me what it's wrong ??</p><p>Please Let me know, Regards</p><p><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" /><br>RB <br></p>
  • Replied 8 September 2017, 2:07 pm EST

    <P>Roberto,</P>
    <P>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.</P>
    <P>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.</P>
    <P>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).</P>
  • Replied 8 September 2017, 2:07 pm EST

    <P>Hello</P>
    <P>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></P>
    <P>Thanks,</P>
  • Replied 8 September 2017, 2:07 pm EST

    <p>I Can Explaint what was happening, but  finally did the project again and it works</p><p>Thanks All for Your Help</p><p>Regards</p><p>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!!!" /> </p><p> </p><p> </p>
Need extra support?

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

Learn More

Forum Channels