#Ref! Error when referencing sheet

Getting help Questions
,
1

Hello,

I have 2 sheets within my React app.

Sheet 2 needs to reference Sheet 1 but there seems to be a race condition as 50% of the time I get a ‘setState’ error which seems to be pointing to a #REF! error in Sheet 2s reference to Sheet 1.

I’m doing something like:

=IFERROR(INDEX(‘CompanyAccounts!B:CompanyAccountsB’,100),0)

I think the issue is due to Sheet 1 being quite large (~300 rows) and hasn’t quite loaded in before Sheet 2 tries to render it.

Is there a solution for this kind of behaviour?

2

Hi @adam2

It would be great top get a demo where we could replicate the issue. But what you can do before creating one is to just postpone the data setup in a useEffect/componentDidMount. Did you try that?

As mentioned in the HyperFormula docs (the engine for calculations that we use) at Types of errors | HyperFormula (v3.0.0)

07
07449×465 50.3 KB

and the possible references are listed here Cell references | HyperFormula (v3.0.0)

3

Thanks for the reply.

Here is a demo to help elaborate (I have tried using a useEffect but to no avail):
https://jsfiddle.net/d8nvo9pz/6/

As you can see there’s a reference error in place of where the SheetName! should be.

Thanks!

4

Thank you for the demo. When we switch the component, so the one with Sheet2 is loaded as second, then it works well. And as mentioned before, the useEffect() seems to work just fine to postpone the data read from the other sheet. Here’s an updated example https://jsfiddle.net/g5sukvf9/. Let me know if I miss anything.

5

Hi @adam2

I hope that the lack of reply means that the issue is solved. If you are still rechecking the solution and need some help, please create a new thread or send me an email at support@handsontable.com