Maximum cross-sheet references

Maximum cross-sheet references

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

Comments

  • 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 WildayMike Wilday ✭✭✭✭✭

    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. 

  • edited 11/29/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.

  • 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 PGenevieve P admin
    edited 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 NewcomePaul Newcome ✭✭✭✭✭

    @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.

Sign In or Register to comment.