I was able to reproduce the error I am experiencing in the online demo http://demos.telerik.com/silverlight/#Spreadsheet/FirstLook so I'll firstly give the steps to reproduce the error there:
1) On the FirstLook Spreadsheet, on Sheet1 in Field A3 I type the formula "=MyFieldName". #NAME? is now displayed in field A3 because we have not defined any meaning for "MyFieldName". So let's define meaning:
2) On Sheet1 I add a name of "MyFieldName" to a cell. I do this by clicking "Formulas" > "Name Manager" > "New". I then type "MyFieldName" as the name of the field and I select the scope as Sheet1 (NOT workbook). I set the "Refers To" field to =Sheet1!$A$1
3) Immediately when I do this, the field A3 no longer displays #NAME? but displays 0 since it now shows the value of the Named Field.
4) I edit field A1 and type the name "Blue" into that field. Field A3 now updates itself and also reflects the word "Blue".
5) Now, I add a second sheet to the workbook. I click on the "+ icon" next to Sheet1 at the bottom of the screen and it adds "Sheet2" to the workbook.
6) I return to Sheet1 and place the cursor on Cell A3. In the menu bar at the top of the screen I select "Home" and "Copy" so that Cell A3 in Sheet1 is copied.
7) I go to Sheet2 and place the cursor on Cell A3 in Sheet2. I now select "Home" and "Paste". When I do this, however, the value "Blue" is incorrectly shown in that cell. If one inspects the formula that was pasted in Cell A3, it correctly pastes the formula "=MyFieldName". However, since the scope of the name "MyFieldName" that we defined in Sheet1 was limited in scope to Sheet1, it should not be accessible from Sheet2. I would expect the field to yet again show #NAME? since it is referring to a Name that does not exist within the worksheet (as we saw when we first added that formula to Sheet1)
The problem has now already been illustrated. But to elaborate a bit more on the problem I'm having with this behavior:
8) I add a Name of "MyFieldName" to Sheet2. In the case of Sheet2 I decide that cell A2 (not A1) should be the field named "MyFieldName". So I go again to "Formulas" > "Name Manager" > "New" and type "MyFieldName" as the Name of the field, I set the scope to be Sheet2 and I set the "Refers To" value to refer to =Sheet2!$A$2
9) After adding the name, I now edit Cell A2 in Sheet2 to contain the word "Red". Now, even after adding "MyFieldName" in Sheet2, the value of Cell A3 in that sheet still reflects the word "Blue". Even though "MyFieldName" in the scope of Sheet2 refers to the word "Red", it still reflects the word "Blue" (which is in the scope of Sheet1).
10) If I click on "File" > "Save" in the menu and I then download my file and open it in Excel, all values are displayed correctly. Yet in the radspreadsheet control the values aren't updated.
11) If I double-click on Cell A3 in Sheet2 and press enter to refresh the value, it updates to the word "Red" as it should. But this doesn't happen if I don't manually press enter again inside the cell after pasting.
In the real-life workbook that I am working with I am creating multiple worksheets from a Template. So I basically paste cell ranges from the Template into the individual worksheets. I also add the necessary "Field Names" to each of my worksheets. The problem I have is that even though I am creating my Names in each of my new worksheets, the cells that I pasted still refer to the Names in the worksheet from which I pasted, even though those names are not in the scope of the current worksheet. If I then manually go and click on each cell and press enter inside of it, it updates correctly with the value in the Name I created, but I cannot do this for each of the cells as there are too many. I have tried to programmatically iterate through each cell in each worksheet and set the Cell Value to the raw string value (which simulates pressing enter) but this is a very inefficient operation and takes nearly an hour to complete, so this is not an option.
This is definitely a bug in radspreadsheet and it would be great if this could be fixed soonest. In the meantime, is there an alternative method of copying and pasting a range of cells (whose formulas may refer to a Named Cell) to a different worksheet so that it uses the names of THAT worksheet instead of the one from which it is pasted?
1) On the FirstLook Spreadsheet, on Sheet1 in Field A3 I type the formula "=MyFieldName". #NAME? is now displayed in field A3 because we have not defined any meaning for "MyFieldName". So let's define meaning:
2) On Sheet1 I add a name of "MyFieldName" to a cell. I do this by clicking "Formulas" > "Name Manager" > "New". I then type "MyFieldName" as the name of the field and I select the scope as Sheet1 (NOT workbook). I set the "Refers To" field to =Sheet1!$A$1
3) Immediately when I do this, the field A3 no longer displays #NAME? but displays 0 since it now shows the value of the Named Field.
4) I edit field A1 and type the name "Blue" into that field. Field A3 now updates itself and also reflects the word "Blue".
5) Now, I add a second sheet to the workbook. I click on the "+ icon" next to Sheet1 at the bottom of the screen and it adds "Sheet2" to the workbook.
6) I return to Sheet1 and place the cursor on Cell A3. In the menu bar at the top of the screen I select "Home" and "Copy" so that Cell A3 in Sheet1 is copied.
7) I go to Sheet2 and place the cursor on Cell A3 in Sheet2. I now select "Home" and "Paste". When I do this, however, the value "Blue" is incorrectly shown in that cell. If one inspects the formula that was pasted in Cell A3, it correctly pastes the formula "=MyFieldName". However, since the scope of the name "MyFieldName" that we defined in Sheet1 was limited in scope to Sheet1, it should not be accessible from Sheet2. I would expect the field to yet again show #NAME? since it is referring to a Name that does not exist within the worksheet (as we saw when we first added that formula to Sheet1)
The problem has now already been illustrated. But to elaborate a bit more on the problem I'm having with this behavior:
8) I add a Name of "MyFieldName" to Sheet2. In the case of Sheet2 I decide that cell A2 (not A1) should be the field named "MyFieldName". So I go again to "Formulas" > "Name Manager" > "New" and type "MyFieldName" as the Name of the field, I set the scope to be Sheet2 and I set the "Refers To" value to refer to =Sheet2!$A$2
9) After adding the name, I now edit Cell A2 in Sheet2 to contain the word "Red". Now, even after adding "MyFieldName" in Sheet2, the value of Cell A3 in that sheet still reflects the word "Blue". Even though "MyFieldName" in the scope of Sheet2 refers to the word "Red", it still reflects the word "Blue" (which is in the scope of Sheet1).
10) If I click on "File" > "Save" in the menu and I then download my file and open it in Excel, all values are displayed correctly. Yet in the radspreadsheet control the values aren't updated.
11) If I double-click on Cell A3 in Sheet2 and press enter to refresh the value, it updates to the word "Red" as it should. But this doesn't happen if I don't manually press enter again inside the cell after pasting.
In the real-life workbook that I am working with I am creating multiple worksheets from a Template. So I basically paste cell ranges from the Template into the individual worksheets. I also add the necessary "Field Names" to each of my worksheets. The problem I have is that even though I am creating my Names in each of my new worksheets, the cells that I pasted still refer to the Names in the worksheet from which I pasted, even though those names are not in the scope of the current worksheet. If I then manually go and click on each cell and press enter inside of it, it updates correctly with the value in the Name I created, but I cannot do this for each of the cells as there are too many. I have tried to programmatically iterate through each cell in each worksheet and set the Cell Value to the raw string value (which simulates pressing enter) but this is a very inefficient operation and takes nearly an hour to complete, so this is not an option.
This is definitely a bug in radspreadsheet and it would be great if this could be fixed soonest. In the meantime, is there an alternative method of copying and pasting a range of cells (whose formulas may refer to a Named Cell) to a different worksheet so that it uses the names of THAT worksheet instead of the one from which it is pasted?
2 Answers, 1 is accepted
0
Stanley
Top achievements
Rank 1
Rank 1
answered on 02 Oct 2014, 03:17 PM
I should also mention, by the way, that this used to work in a previous version of Telerik radspreadsheet (one or two releases back, if I remember correctly).
0
Hi Stanley,
Thank you for the detailed explanation.
I have tested the described behavior in our online examples and I was able to reproduce the issue. The issue will be fixed and will be released with the next official release Q3 2014.
Regards,
Nikolay Demirev
Telerik
Thank you for the detailed explanation.
I have tested the described behavior in our online examples and I was able to reproduce the issue. The issue will be fixed and will be released with the next official release Q3 2014.
Regards,
Nikolay Demirev
Telerik
Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
Kenneth
commented on 27 Sep 2023, 09:41 AM
Top achievements
Rank 1
Rank 1
Dear,
I found the same problem now, my Excel define as below, and while I run in the program, the GetResultValueAsString always returned the first column (69.1)
Program result:
So we can see that even Column changed, the sValue is the same... so anyone can help?