Maximum cross-sheet references

Steve Moss · 11/25/18

Once you reach this figure of maximum cross sheet references how can you get around this.

I want to configure calculations to 2030. Got to June 2025 before I got stuck.

Regards

Steve

Mike L. · 11/26/18

Can you describe how you reached the limit? Do you have one sheet for each (future) year? Are you using formulas to reference a single sheet? How many forumlas on each page calling the reference sheet?

I'm not sure I have anything helpful to add but I have not hit the 25K limit yet but I'm worried that I'm going to design my way into a corner soon.

Mike Wilday · 11/26/18

Breaking your work up into segmented chunks is the safest bet to ensure you do not reach limits. We are currently breaking our projects into yearly sheets which is currently working for us and preventing this from happening. Clearly Smartsheets were designed to focus on smaller sets of more detailed data, but I am encouraged to see some communications from Smartsheet indicating they are looking at the possibility of implementing changes to allow for larger data sets in the future.

Steve Moss · 11/28/18

I am recording incidents and injuries, near misses and opportunities etc (a complex range of data on a main register).

I have a sheet that functions out by a date range each separate classification.

This work fine until it reaches too many.

I need to keep the main register and want to run it for the next 12 years without too much change. How ever the limit has prevented this. I can only get to 2026 June.

This sheet uses cross references and a lot of them. Even if I split it, the main register where all the data comes from its pushed.

I tried to break it down to smaller cross reference sheets but still happens.

As you know, a spreadsheet can do this, so if indeed this is a smart sheet, then why the limit. Just doesn't make sense to me.

April Biolsi · 02/05/20

I'm also having an issue with this, and it's severely limiting.

I'm trying to use the vlookup function to update from a separate project management software platform, and get the data to update the shared smartsheet. I can't get past the 6th column for reference.

Genevieve P. · 02/05/20

Hi @April Biolsi

Instead of using a VLOOKUP that has to read through a table, could you use an INDEX(MATCH formula instead? This way the formula is only looking at two columns and not a whole reference table.

An INDEX(MATCH works like this:

=INDEX({Column that has the value you want returned}, MATCH([Value you’re looking to match]@row, {Column with the value you’re looking to match in the other sheet}))

Paul Newcome · 02/05/20

@April Biolsi

I agree with @Genevieve P. The INDEX/MATCH can go a long way in alleviating your problem.

In addition to possibly referencing fewer cells by only referencing the two columns needed instead of everything in between, INDEX/MATCH also provides a greater level of flexibility.

Because you are not referencing a column number as you do in VLOOKUP, you can rearrange the columns on the source sheet as needed without fear of breaking your formulas.

You also do not need to worry about which one is on the left and which one is on the right like you do with VLOOKUP.

Once you get the hang of INDEX/MATCH, you'll never use VLOOKUP again. If you would like a further breakdown of how it works or don't quite understand the syntax, don't hesitate to ask.

ssagers · 04/07/22

@Paul Newcome - Can you tell me how many cross sheet references are counted towards the maximum in the INDEX/MATCH formula below? Since they're all pointing to the "Active Listening" sheet, do they all count as one reference, or do I need to count each time I use a reference in the formula separately (meaning I would have 4 in my example)? Thanks for your help!

=IFERROR(IF(INDEX({Active Listening Range 2}, MATCH(Email@row, {Active Listening Range 1}, 0)) = "Reviewed with CSR", "Green", IF(INDEX({Active Listening Range 2}, MATCH(Email@row, {Active Listening Range 1}, 0)) = "Add to 1:1 list", "Yellow", "Red")), "")

Paul Newcome · 04/08/22

@ssagers It looks like you have 2 unique references. The number of cells that count towards the limit will be variable depending on the actual size of each range.

Warren Labuschagne · 08/15/22

@Paul Newcome what is the maximum number of cross-sheet references allowed per sheet? I currently have one sheet (Creditors) who references values from many other sheets. We have 56 projects and we work out how much is due based on the % complete which is found on the schedule for the project, an example of the formula is; =SUM(48756.55 * {1522LV PC Items Range 2} - 39005.24). There would about 30 cross-sheet references per project (56x30=1680). We are already getting error messages and we haven't linked all the sheets yet. What is another way to reduce the amount, if we have reached the limit?

Andrée Starå · 08/15/22

Hi @Warren Labuschagne

I hope you're well and safe!

A sheet can include no more than 100 distinct cross-sheet 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!

Warren Labuschagne · 08/15/22

https://community.smartsheet.com/discussion/comment/339619#Comment_339619

@Andrée Starå thanks for the response. Is there any workaround that you know of for me to be able to achieve what I am trying to do?

Paul Newcome · 08/15/22

@Warren Labuschagne Have you tried creating a row report, grouping it, then adding in the summary fields?

Warren Labuschagne · 08/15/22

https://community.smartsheet.com/discussion/comment/339651#Comment_339651

@Paul Newcome yes, I have tried a report, however that doesn't work for this instance, unless I'm not doing it right. I have changed some of the cross-sheet references to direct cell references which has reduced the cross-sheet references, however I will need far more. If I used INDEX formulas, would this reduce the amount of cross-sheet references? If I am referencing the same column in multiple rows, could I use the same reference name to reduce the amount of cross-sheet references?

Waleed · 09/04/22

Hi @Andrée Starå

I should brought to your attention that im newbie with this :)

I created new sheet and would like to copy selected columns from attached sheet , lets assume i would like to copy from the sheet (Orders Live ) the Columns Time, From , To and Passengers to helper sheet (Helper Orders Live ) what formula would be ? Thank you

Andrée Starå · 09/04/22

Hi @Waleed

I hope you're well and safe!

Do you only want to have the values shown, or should they be editable?

I hope that helps!

Be safe, and have a fantastic weekend!

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!

Maximum cross-sheet references

Comments

Help Article Resources

Categories