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

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!

In this Discussion