Dynamically set column labels and be able to use them in formulas

Posted by: cyndichatman on 8 September 2017, 5:22 am EST

  • Posted 8 September 2017, 5:22 am EST

    We have a spread with multiple sheets.  In each sheet, we have a varying number of columns, but each sheet also contains columns of data that users will refereence using formulas.  For example, in sheet 1, we may have 3 "descriptive" columns and then numbers begin in column 4.  In sheet 2, we may have 4 "descriptive" columns and the numbers begin in column 5.  This means that the label for the first number column in sheet 1 is D, while in sheet 2, it is E.  To make it easier for the user, we would like to standardize that the first number column in each sheet is always labeled "AA".  Meaning, in sheet 1, the column labels would be A, B, C, and then jump to AA.  In sheet 2, the colun labels would be A, B, C, D, and then jump to AA.  I wrote a function to automatically increment the column labels to change D to AA and E to AB (in sheet 1) and so on.  I then called a function to set the column label.  However, formulas reference the column's true index.  That is, the 4th column in sheet 1 is still D internally.  Is there a way I can fake the column labels and have the formulas deal with them via the new name?

     I don't really want to add empty, invisible columns as this will cause a lot of junk data in my database app and could impact performance.

  • Replied 8 September 2017, 5:22 am EST


    There is no way to fake out the formulas to use your custom labels.  The formulas use an Excel-like syntax which always uses the orginal default labels (i.e. A, B, C, D, E, etc).

    Note that Spread does support row headers with multiple columns (and column headers with multiple rows).  One option might be to create multiple columns in the row headers and move the discriptive labels into the row headers.  Then the data could always start in column A.

    For example, the following code would create two sheets.  The first sheet has row headers with two columns.  The second sheet has row headers with three columns.  Both sheets have data starting at column A.

                fpSpread1.Sheets.Count = 2;

                fpSpread1.Sheets[0].RowHeader.ColumnCount = 2;
                fpSpread1.Sheets[0].RowHeader.Cells[0, 0].Text = "Label0";
                fpSpread1.Sheets[0].RowHeader.Cells[0, 1].Text = "Label1";
                fpSpread1.Sheets[0].Cells[0, 0].Value = 11;
                fpSpread1.Sheets[0].Cells[0, 1].Value = 22;
                fpSpread1.Sheets[0].Cells[0, 2].Formula = "A1+B1";

                fpSpread1.Sheets[1].RowHeader.ColumnCount = 3;
                fpSpread1.Sheets[1].RowHeader.Cells[0, 0].Text = "Label0";
                fpSpread1.Sheets[1].RowHeader.Cells[0, 1].Text = "Label1";
                fpSpread1.Sheets[1].RowHeader.Cells[0, 2].Text = "Label2";
                fpSpread1.Sheets[1].Cells[0, 0].Value = 33;
                fpSpread1.Sheets[1].Cells[0, 1].Value = 44;
                fpSpread1.Sheets[1].Cells[0, 2].Formula = "A1+B1";

  • Replied 8 September 2017, 5:22 am EST

    Hi Bobbyo -

     I don't think I can use that method as the columns that have the descriptive labels also contain user modifiable data.  That is, the data in these columns is text (not numbers), but the user has to be able to modify the text.

  • Replied 8 September 2017, 5:22 am EST


    As stated by Bobbyo, putting the labels in Column of Row Headers seems to be the only work around to this as it is not possible to use the custom labels for Row and Columns in Formulas.





Need extra support?

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

Learn More

Forum Channels