Two types of problems occur when entering a cross-spreadsheet reference formula

Desktop editors
1

Two types of problems occur when entering a cross-spreadsheet reference formula.

I use
desktop editors 9.1.0.167 (x64 exe)
windows 11 pro 24H2

video ONLYOFFICE

0:00 Create two new spreadsheets
0:14 Entering a cross-spreadsheet reference formula → Unable to complete
0:34 Save one spreadsheet
0:46 Entering a cross-spreadsheet reference formula → Unable to complete
0:56 Save another spreadsheet
1:07 Entering a cross-spreadsheet reference formula → Success
1:24 Entering a cross-spreadsheet reference formula → Invalid workbook name specified

2

Hello @nishigaki

I may assume the issues are:

  1. Unable to use referenced cells before saving of both files;
  2. #REF! appearing.

For the first one: it is expected, because newly created files do not exist in the file system and the changes made to them are not saved, so the values for referencing do not exist at this moment.

Second one, if I get it right, is a bit unclear. What exact actions you do at 1:27 mark? I can see that the formula editor is opened, but the formula only contains = sign, while it should have the reference link. Can you share more detailed steps?

3

Hello @Constantine

Supplement the report.

0:00 Create two new spreadsheets
0:14 Entering a cross-spreadsheet reference formula → Unable to complete

-> I can complete the formula in MS Excel.

0:34 Save one spreadsheet
0:46 Entering a cross-spreadsheet reference formula → Unable to complete

-> The values for referencing exist in e.xlsx.

0:56 Save another spreadsheet
1:07 Entering a cross-spreadsheet reference formula → Success
1:24 Entering a cross-spreadsheet reference formula → Invalid workbook name specified

-> I selected '[e.xlsx]シート1'!$B$2, but '[ブック6.xlsx]シート1'!$B$2 was set.

The question at 1:27 is whether the formula “=‘[e.xlsx]シート1’!$B$2” becomes “=”? When I select the tab “e.xlsx”, the formula becomes “=”.

4

I see, so this is issue number 1.

I am not following this question. Do you use any keys, for instance, to edit the cell at that moment? I my tests it produces different behavior – the formula still contains a reference link.

5

Hello @Constantine

I’m doing the following:
1:27 I type F2 key to edit cell.
1:30 click “e.xlsx” tab.
1:33 click “B2” cell.

6

I see, thank you. We’re checking this behavior, please await my feedback.

8

So the issue with #REF! is a bug, it has been registered.

However, the first problem, which is the inability to use reference links to an unsaved file, is expected. As I mentioned, newly created file does not exist in the file system, thus there is nowhere to build a reference to.

9

Thanks @Constantine

I look forward to the #REF! issue being resolved.

I also have understood the requirements for reference links.
However, since MS Office allows to create links between unsaved files, those familiar with MS Office may not understand this symptom. I’m not sure how to make them understand the symptom, though.

1 Like
10

It is possible that a warning would be added to ensure that, but thats for the future updates.

11

Thanks @Constantine

Yes, it’s difficult to find a method that works for everyone.

1 Like
12

Hello @nishigaki

The issue with #REF! has been resolved in new version 9.3 of Desktop Editors.

13

Thanks @Constantine

The #REF! issue has been resolved.
But the reference value is not updated when I change the source, nor is it updated when I save the source file or click the calculate button. as follows:
https://nisigaki.onlyoffice.com/s/F96dXcbwgdsL5pN
0:14 set reference formula in b.xlsx
0:27 change source value in a.xlsx
0:30 but not updated b.xlsx
0:38 click calculate but not updated
0:45 save source file a.xlsx
0:49 not updated b.xlsx
0:54 click calculate but not updated
1:04 reenter reference formula in b.xlsx
1:09 display correctly

14

Would you mind checking whether from Data tab > External links > Update values values are updated?

15

Thanks @Constantine

I saved the source file and clicked Update Values, and the cell values ​​were updated.

However, this is a problem for those familiar with MS Excel, as in MS Excel the cell values ​​are updated without saving the source file or clicking Update Values. Are there any plans to make it work the same as MS Excel?

16

Thanks for the reply. We’ll discuss this difference internally and I’ll provide a feedback.

18

We have registered an enhancement for the suggestion to implement an update of external data in real time like in mentioned processor. I will let you know once it is implemented.

19

Thanks @Constantine

I look forward to it being implemented.

2 Likes