Cross-sheet formulas can't be updated

Jonathan Dayan

Hi all,

We have a sheet that references a lot of data on other sheets using index and match functions. Recently, we began receiving the below notice and many cells in the sheet come up as "#calculating."

What do you propose as a workaround to this issue? Thank you in advance

Andrée Starå

Hi @Jonathan Dayan

I hope you're well and safe!

I would need to have more information to give a better recommendation, but one thing you could potentially do is to combine multiple columns to one and then break them out in the destination sheet(s) instead.

I recently did this in a client solution with a huge and advanced price list.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jonathan Dayan

https://community.smartsheet.com/discussion/comment/292805#Comment_292805

Thank you for the response, Andree! To elaborate further, we have referrals that are logged in a main sheet. When some of those referrals reach a final status (non-admit, discharge, etc.) they transition to a finalized sheet on a weekly basis (every Friday night). The destination sheet in question is specifically for one vendor to keep abreast of their referrals. The fields for each referral are brought in to the destination sheet through column formulas (example: =IFERROR(INDEX({Intake Tracking Range 1}, MATCH(EPIID@row, {Intake Tracking Range 2}, 0)), INDEX({Intake Tracking - Finalized Cases Range 1}, MATCH(EPIID@row, {Intake Tracking - Finalized Cases Range 2}, 0))) There is too much data on the two source sheets to which the destination sheet refers.

Hope this clarifies things a bit. Can you please elaborate on how we would combine multiple columns into one and them break them out?