Use value of a different sheet cell to calculate total.

[nilesh-swivel]

There is an application with multiple sheets; one sheet contains (groups) some codes with cell references. We are calculating the usage count for each code on each sheet and the total if the code is used in the aggregation sheet. problem is sometimes we need to use that total in a different sheet, which might be used to calculate the above-mentioned same total value. But in that scenario, we are not using a required code to calculate the total, but I still get the CYCLE error. In Excel, I can perform the task without an issue. I have attached my code and the Excel sheet mentioned for reference.

Link to Excel Sheet: https://docs.google.com/spreadsheets/d/1DaaCdFw5-EONoFRI2RQt0gvfhJHhT8rV/edit?usp=share_link&ouid=102668536492449895575&rtpof=true&sd=true

Code Sandbox: https://codesandbox.io/s/pivot-budget-aggregation-forked-c15xeu

We need to use this total as a cell reference is a sheet, which contributes to this total (but not that exact row). if you need any clarification, feel free to ask.

[sequba]

Hi @nilesh-swivel, your example contains a lot of code and it's very hard to analyze. Particularly, I don't see exactly where the #CYCLE error is displayed.

Can you create a smaller example?

[nuwanb-swivel]

Thank you @sequba for the quick replay. We will look into alternative solutions.

Does cell aggregations!M3 need to be dependent on groups!E3 value? - Yes. this is something we are trying to do.

[kevinplummer]

Any plans to make it compatible with Excel?

[sequba]

Any plans to make it compatible with Excel?

Unfortunately, this would require redesigning a significant part of the engine, so we do not plan to do it in the near future.

[kevinplummer]

Thanks - it would be helpful to add this to the compatibility section of the documentation.

[sequba]

In Known limitations page we have:

For cycle detection, all possible dependencies between cells are taken into account, even if some of them could be omitted after the full evaluation of expressions and condition statements. The most prominent example of this behavior is the "IF" function which returns a cycle error regardless of whether TRUE or FALSE causes a circular reference.

It is also mentioned in the Runtime differences page.