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.
Best Answers
-
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
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Answers
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Dear Paul,
Thanks for your support as well. 🏆️
Best regards.
-
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.
-
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives