Spreadsheet formulas using data from multiple tabs

Webix JS
1

I am evaluating Spreadsheet for inclusion in an internal web app and I have 2 questions/issues. If I can’t resolve them, it will be a show-stopper.

  1. Referencing cells on 2nd sheet
    When creating a formula, I can easily click on a cell in the same sheet and its reference in included in the formula. Very good. However, from what I have seen this is not the case when I want to use a cell that is on a 2nd sheet. I can’t just go to the cell and click on it and the reference in included in my formula. Am I doing something wrong or is that expected behaviour?

  2. Insert lines on 2nd sheet doesn’t update formula reference
    Assume I have a formula on Sheet1 that contains a reference to say Sheet2!A1. Now on Sheet2, I insert a new column at A. So, the cell I want to reference is now Sheet2!B1. As far as I can tell, the formula back on Sheet1 does not get updated to reference Sheet2!B1 like Excel does. Again, am I doing something wrong or is that expected behaviour?

Can you please provide some guidance as soon as possible.

Many thanks

2

Hi @geyres

  1. Yes, there is no such built-in feature, but you can try something like that: https://snippet.webix.com/k3ri3vc7

  2. Yes, this feature isn’t implemented, so it’s expected behaviour. We added this to the list of features and in future versions we will try to implement it.

3

OK, Thanks Dima_S
Your solution to the 1st issue works beautifully - brilliant. Any idea on the timeframe to address the 2nd issue? Or, perhaps a work-around?
Cheers

4

@geyres

We are planning to rewrite the spreadsheet’s mathematics, but it’s a rather big part of the code, so we can’t tell the timeframe.

Workaround:

You can attach inner onCommand event to check if row/col was added/removed, serialize spreadsheet, modify records and parse the updated data.

https://snippet.webix.com/q56koiav

5

Thanks Dima - understand. There seems to be a bug in the workaround. If I can get that working, that would be great and solve my issue. Many thanks.

6

@geyres

There seems to be a bug in the workaround

Pay attention that some formulas in my example have absolute cell reference.

7

Thanks Dima - I can see what is happening now …
The snippet works really well when I am inserting a line into Sheet2. However, in addition, I need to be able to insert a line into Sheet1 and the formulas in Sheet2 are updated. I tried to update your code but I can’t get it to work. I’m sure it isn’t too much of a change but I am only new to this ;(
Really appreciate it if you could have a look. Thank you so much.

8

@geyres

However, in addition, I need to be able to insert a line into Sheet1 and the formulas in Sheet2 are updated.

As I already said, some formulas in the second sheet have absolute cell reference. In your case you need relative, so just not use $.

Example with explanations: Code Snippet

9

Hi Dima
I have found the issue! Your solution works absolutely perfectly on my version of Firefox (v67.0.4). However, on my version of Chrome (v72.0.3626.121), I get the following error:
Uncaught TypeError:
data[2].matchAll(…) is not a function or its return value is not iterable at line 34
The spreadsheet does not even load in my version of IE (v11.0.9600.19356CO).
I have users using all sort of different browsers. Is there something we can do to make it work across all 3 of these?
Many thanks

10

@geyres

The spreadsheet does not even load in my version of IE (v11.0.9600.19356CO)

You should remove arrow functions and matchAll method (as they are not supported by IE).

Updated example: Code Snippet

11

Hi Dima - all your answers work really well, but I have a related question …
If I have a formula in Sheet1!A1 that is =Sheet2!A1, then I copy Sheet1!A1 to Sheet1!A2, the formula in Sheet1!A2 remains as =Sheet2!A1.
I need the formula in Sheet1!A2 to be updated to Sheet2!A2, just like Excel does. Seems to me it might be similar solution to what you have previously provided above??

12

@geyres

I can confirm the issue, we will fix it. Unfortunately, there is no easy workaround.

13

OK - thanks Dima. Any timeframe on the fix? Within the next few months would be great :wink:

14

@geyres

I think this fix will be released in version 7.0 or in one of the 6.4 minor versions.

15

@geyres

We fixed the described problem in Webix version 6.4.5.

16

Referencing cells on 2nd sheet
When creating a formula, I can easily click on a cell in the same sheet and its reference in included in the formula. Very good. However, from what I have seen this is not the case when I want to use a cell that is on a 2nd sheet. I can’t just go to the cell and click on it and the reference in included in my formula. Am I doing something wrong or is that expected behaviour?

@geyres , starting from Webix 7.0 this feature works out of the box.