I am getting Cross-Sheet formula issue which is limited to Maximum Allowed (100000)

Dear All,

I am getting below error message.

"Some cross-sheet formula can’t be updated, because this sheet has more cell referenced from other sheets than the maximum allowed (100000)"

How can I fixed the above issue since I am maintaining history table and all completed records I am taking from historical table using Vlookup formula.

Need your suggestion and support.

Thanks and best regards.

Find more posts tagged with

Formulas

Accepted answers

Paul McGuinness

Hello @Ibrahim Khaleel

Have you tried changing from a vlookup to index match instead? as that may reduce the number of cells being referenced.

There is a good tutorial here - https://community.smartsheet.com/discussion/84774/index-and-match-across-two-sheets-a-detailed-explanation

Hope that helps

Andrée Starå

@Ibrahim Khaleel

Excellent!

Happy to help!

Ibrahim Khaleel

All comments

Paul McGuinness

Hello @Ibrahim Khaleel

Have you tried changing from a vlookup to index match instead? as that may reduce the number of cells being referenced.

There is a good tutorial here - https://community.smartsheet.com/discussion/84774/index-and-match-across-two-sheets-a-detailed-explanation

Hope that helps

Andrée Starå

Hi @Ibrahim Khaleel

I hope you're well and safe!

To add to Paul's excellent advice/answer.

If needed, you could collect multiple columns in one and then split it out again in the destination sheet. I've done that a few times in my client solutions, even if I haven't hit the limit because it's less resource heavy.

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ibrahim Khaleel

Dear Paul/Andree,

Thanks for prompt support.

I will apply with test data and if works then replicate the same to Production one.

I will confirm once applied.

Best regards.

Andrée Starå

@Ibrahim Khaleel

Excellent!

Happy to help!

Ibrahim Khaleel

Dear Andree,

As per your suggestion, I have replaced Vlookup with INDEX AND MATCH formula (for few columns) and now error notification is disappearing.

Old Formula: VLOOKUP([Submitted by]@row, {Refund Request History Range 1}, 2, false)

New Formula: INDEX({Submitted Email ID},MATCH([Submitted by]Row,{Submitted by},0))

Now I will replace all Vlookup formula with Index & Match combination.

Thanks for your prompt support. 🏆️

Best regards.

Ibrahim Khaleel

Dear Paul,

Thanks for your support as well. 🏆️

Best regards.

Ibrahim Khaleel

Dear Smart Team,

I am getting below error message again which cause not triggering workflow automatically.

"Some cross-sheet formula can’t be updated, because this sheet has more cell referenced from other sheets than the maximum allowed (100000)"

How can I get the permanent solution to avoid this error.

Thanks for prompt support and appreciate in advance.

Best regards.

Ibrahim Khaleel

SS_New

@Andreea

I am struggling with the issue of 100,000 cross sheet reference. I see that you have suggested to use

If needed, you could collect multiple columns in one and then split it out again in the destination sheet. I've done that a few times in my client solutions, even if I haven't hit the limit because it's less resource heavy.

I tried using JOIN but was not able to get the results, any help/ suggestion would be highly appreciated.

Thanks

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.