When pasting formulas containing named ranges in different sheets, the formula in the source is changed to refer to the destination worksheet name

1 posts, 0 answers
  1. Stanley
    Stanley avatar
    3 posts
    Member since:
    Aug 2014

    Posted 30 Oct 2014 Link to this post

    This is a new issue that was introduced in UI for Silverlight Q3 2014 and is partly related to the issue I reported here. It is also rather urgent as this is causing our client's calculation engine to not work anymore...

    I have an Excel Document that acts as a "Template Worksheet" in which users have set up various formulas to evaluate data. I use radSpreadsheet to import this Template and paste the formulas into various other spreadsheets that I generate from my application in order to apply the user's "Template Formulas" to the data on the system-generated worksheets.

    Some of the user's formulas refer to cells that represent named ranges.

    Now, in my "Template Worksheet" I might have a cell with the formula:


    where MyField is defined as a Name that is Scoped only to the "Template Worksheet".

    I then paste the cell with the formula in my newly generated worksheet, say, "My New Sheet Added In Code". (My idea is then to manually insert a Name into that worksheet as well that is called "MyField" where "MyField" could refer to a different cell than in the template.)

    The problem is that the moment I paste the formula cell in the new spreadsheet, the formula in both the target worksheet as well as in the source ("Template Worksheet") changes from:


    to the following:

    =IF(ISERR(MIN(B1,IF('My New Sheet Added In Code'!MyField<B2,0,('My New Sheet Added In Code'!MyField-B2)/B3*B4))),0,MIN(B1,IF('My New Sheet Added In Code'!MyField<B2,0,('My New Sheet Added In Code'!MyField-B2)/B3*B4)))

    This doesn't happen with simple formulas for example "=MyField" but it does happen in the formula I have provided.

    I have written a very short 3.9MB Silverlight Application that illustrates the problem, but I see I cannot attach it to this post. As such I am including the relevant code segment below (but I am sure that if I can upload the small application it will more clearly immediately show the problem)

    XlsxFormatProvider formatProvider = new XlsxFormatProvider();
    Workbook workbook = formatProvider.Import(e.Result);
    radSpreadsheet.Workbook = workbook;
    //Select and Copy from the Template
    Worksheet templateWorkSheet = workbook.ActiveWorksheet;
    CellSelection allCellsToCopy = templateWorkSheet.Cells[0, 0, 10, 3];
    WorksheetFragment copyFragment = allCellsToCopy.Copy();
    //Add a new worksheet where I can play around with data and change some values
    Worksheet worksheet = workbook.Worksheets.Add();
    workbook.ActiveWorksheet = worksheet;
    worksheet.Name = "My New Sheet Added In Code";
    //Now I paste the template to act as a "calculator" to apply the user's formulas from the template to the new worksheet
    worksheet.Cells[copyFragment.CellRange].Paste(copyFragment, PasteOptions.All);
    //In the new worksheet, the name "MyField" should refer to field A2 and not A1 as in the template //I am only including this line to show my planned usage. The problem occurs even without it.
    worksheet.Names.Add("MyField", "='My New Sheet Added In Code'!$A$2", new CellIndex(0, 0), "My Field");
Back to Top