3 Replies Latest reply on Apr 26, 2012 10:59 AM by -==cfSearching==-

    Can you use the R1C1 reference style for SpreadsheetSetCellFormula

    HugoSchmidt Level 1
    HugoSchmidt Apr 26, 2012 10:10 AM

      I am creating a spreadsheet where I want the sum of some columns to appear at the end of the row.  The problem is that the number of columns can vary depending on when the report is run.

       

      A SpreadsheetSetCellFormula function is written like this for summing Columns B through D and placing the function in column 5 (E).

       

      <CFSET curRow = 2>

      <CFSET curCol = 5>

      <CFSET SpreadsheetSetCellFormula(sObjRollUp,"SUM(B2:D2)",curRow,curCol)>

       

      But what if there are 10 columns, or 15 columns?

       

      I tried this and it did not work using the R1C1 reference style:

      <CFSET SpreadsheetSetCellFormula(sObjRollUp,"SUM(R#curRow#C#curCol-4#:R#curRow#C#curCol-1#)",cur Row,curCol)>

       

      But that generates an Excel Error.

       

      I know that (in Excel), if I turn on R1C1 reference I can reference a Sum range like this: =SUM(R6C2:R6C3), but if the R1C1 reference is turned off, this function won't work.

       

      So a couple of questions.

      1) Is there CF or Excel function to get the "LETTER" value from a numeric column value (don't forget about columns AA, AB, etc).

      OR

      2) Is there a CF way to turn on R1C1 referencing in the spreadsheet object?