Spreadsheet formulas using data from multiple tabs

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

Tagged:

Comments

  • 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.

  • 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

  • edited July 2

    @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

  • 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.

  • @geyres

    There seems to be a bug in the workaround

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

  • 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.

  • edited July 5

    @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: https://snippet.webix.com/yzmotfnh

  • 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

  • @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: https://snippet.webix.com/01795cw3

  • 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??

  • @geyres

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

  • OK - thanks Dima. Any timeframe on the fix? Within the next few months would be great ;)

  • @geyres

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

  • @geyres

    We fixed the described problem in Webix version 6.4.5.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!