Cross Sheet Reference - Error for more than 100000 references

Shrikant Kekane · 08/18/22

Hello,

We have a base sheet which contains around 9000 rows. We are generating some statistics and KPIs from that base sheet in another sheet.

There are total of 17 managed references in the KPI sheet where all computation is done. Somehow, we are now getting a warning message as below.

Also, the cells show as below.

Can someone please help and suggest, what could be the cause of this and how to fix this?

Note - The data will gradually increase over time. It is yearly data, so we could start from scratch as the year ends. All the managed references are active and needed. They do not repeat. I did verify that.

Hollie Green · 08/18/22

Sounds like you have some formulas in the base sheet as well which is adding referenced cells. My suggestion would be to set up an automation to copy rows from the base sheet to another sheet to pull in the data for KPI statistics.

Andrée Starå · 08/18/22

Hi @Shrikant Kekane

I hope you're well and safe!

How many cells are you referencing from other sheets?
What formulas are you using? (we could maybe simplify them to save references)

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!

Shrikant Kekane · 08/23/22

https://community.smartsheet.com/discussion/comment/340204#Comment_340204

Ok, so do you mean an exact copy of the base sheet? with the data? Is there a way in smartsheet to do that, without using VLOOKUP. There are around 15 to 20 columns, so applying VLOOKUP with slow it even further right?

Shrikant Kekane · 08/23/22

https://community.smartsheet.com/discussion/comment/340211#Comment_340211

Hello Andree,

Base Sheet - Total columns - 29 ( 6 formulated columns, and rest are directly fetched using the workflow from JIRA connector)

Major formulas used in those 6 columns formulas are VLOOKUP, IF, VALUE, YEAR, MONTH, etc.

Total Rows in base Sheet are 9500.

Do you see any specific issue, which I can work on to fix it or make it more efficient?

Regards,

Shrikant

Andrée Starå · 08/23/22

@Shrikant Kekane

How are the other sheets referencing the main one? With VLOOKUPs?

Do you need the whole range that the VLOOKUPs are referencing?

Have you explored using an INDEX/MATCH structure instead?

✅Remember! 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!

Hollie Green · 08/23/22

As long as the data doesn't change after it is added to the sheet the first time getting an exact replica is pretty easy. You just copy your current data and set up an automation to copy the row anytime one is added from this sheet to your new sheet.

The Index/Match and Index/Collect functions are good ones to use instead of VLOOKUP in many cases as well. I use those quite often. Wasn't aware it reduced referenced cells I just like it because when I move a column or add a column I don't have to redo the formulas.

Cross Sheet Reference - Error for more than 100000 references

Answers

Help Article Resources

Categories