Maximum number of cross-sheet references per sheet

Chris McKay

Dear Smartsheet,

Please tell me that the below error is just a soft cap like the 5,000 cell-link limit.

I probably have 100 unique cross-sheet references in a metadata sheet I've been putting together. I am about 90% of the way through and just encountered the below.

The way my sheet has been structured makes it impossible to use without having all the cross-sheet references in place. Had I known there was a limit, I would have taken another (albeit less functional) tact. The templates, underlying metadata and control centre config is quite complex, so really cannot be analysed here.

Excel Online limits cross-sheet references only by available memory, as does Google sheets. While I'm not expecting miracles, 100 references per sheet seems a little miserly. If this is a functional limit, why wasn't it made clear in the release notes, announcements or contextual help? I've even touted this feature as a deal breaker for our Finance team, so I'm going to be pretty embarrassed to backtrack as they will more than likely breach 100 references. 

Recent events have me seriously doubting your commitment to customers and a communications oversight like this doesn't help. How can I stand in front of your product when I keep getting kicked under a bus?

Daniel Stein

Hi Chris,

There are 2 relevant limits for cross-sheet formulas:

• A total of 25,000 cells can be referenced in from other sheets.
• A total of 100 distinct cross-sheet references can be created on a sheet.

The first limit is documented in our support article, but it looks like the second isn't yet. We apologize for this inconvenience and will get that added. Thanks very much for reporting the issue.

For context, in our user research we found that these limits accommodate most use cases for cross-sheet formulas. Several customers have requested that we raise the 25,000 limit, and this is something we are looking into. We haven't really heard requests to raise the 100 distinct reference limit, because most common cross-sheet formula use cases require only a handful of references. (If you use the same reference more than once, e.g., as a lookup table, it only counts once against the limit.) 

Would you mind sharing some additional info about how your sheet is structured and how you are using the references?

Best regards,

Daniel

 

Chris McKay

Hi Daniel,

Thanks for the quick reply and the clarification and apologies in advance for the length of what I am about to write.

While I understand that your research indicated the limits would accommodate "most use cases", there needs to be a greater degree of tolerance built-in (i.e. over-specification) when it comes to these kinds of settings. It's not unreasonable to expect a sheet to have more than 100 cross-sheet references. Nobody could possibly predict all the use cases, requirements or scenarios under which Smartsheet will be employed at the myriad of customers you have. As Smartsheet is marketed as an enterprise-grade product, the constraints described here are pretty lean.

As a summary of what I'm doing purely to help your Product Team understand what we're doing:

• I have developed 3 project level metadata templates (used by SCC)
• One is used to capture project intake data, one is used for financials and one main sheet is used to handle the remaining dynamic project data
• These metadata sheets feed into a Reporting Metadata sheet (configured in SCC) where information about all the projects within our entire portfolio exists (and can be reported on).
• I have also created a project level Consolidated Reporting sheet that pulls information from the 3 project metadata sheets into a kind of "Dashboard and Reporting Shopping Cart", where all the project information can be accessed in a single location and can be accessed by external systems such as Power BI or NetSuite (if required).
• I also have a number of templates in SCC groups (e.g. Project Plans, Financials) that suit particular applications (e.g. Simple Project Plan, Agile Sprint Plan, Detailed Financials etc.)
• Because the Metrics widget used on dashboards can only pull data from fixed cells (i.e. not dynamically via reports or formulas) and can only specify interaction with one particular sheet/report, I have created separate Project Team dashboards that pull data from and link to only the provisioned templates (e.g. Sprint Plan)
• This also affects limits our ability to create dynamic navigation buttons (i.e. images with interactions) in the same manner
• To ensure my project level Consolidated Reporting sheet and the 3 project level metadata sheets were not showing loads of errors caused by cell-links to templates that were not provisioned, I am using validation formulas to determine whether a source sheet/cell exists, e.g.:
  
  =IFERROR(IF(LEN({Simple Project Plan Version}) > 0, {Simple Project Plan Start Date}, ""), "")
• Cell links alone will not work as they will display errors if the source cell does not exist and the Consolidated Reporting sheet is user-facing. Cell-links do not allow me to enter formulas to counter this (e.g. IFERROR), so I need to fall back on cross-sheet references.
• As a working example, if select the Simple Project Plan template in SCC during provisioning, I want the metadata templates to check for the existence of all 3 project plans (Agile, Detailed and Simple) and only pull data from the one that has been provisioned (see the image below).

Now I can fix this and have already started doing so. I don't really need assistance or advice on how this could be achieved and the above is a very simplified account of what we have developed and our requirements. I also completely understand that the solution we have implemented is way, way more complex than almost every other Smartsheet instance out there. But the point is that we designed and developed it based on the assumption that we did not have hard limits on cross-sheet references.

As an aside, the 25,000 cell-link hard limit will definitely be an issue for us in the near future. Based on your advice that you have had other customers requesting an increase, I strongly urge your Product Team to prioritise this (and other critical bugs we're screaming out for) instead of less important initiatives like slightly shifting the colour palette hues or the recent UX change (seriously, review the feedback on the forum about this and feed it back to the Product Team). Those asking for an increase are more than likely some of your largest customers. I cannot imagine they'll be happy to cop it on the chin when the system grinds to a halt and Smartsheet is busy trying to hurriedly conceptualise, develop and implement a fix.

We have developed a Smartsheet Control Center based solution originally with the assistance of Smartsheet. The vast majority of work was undertaken before I was involved and before I learned the Smartsheet ropes. Standard practice for configuring SCC is to roll up reporting into a central Reporting Metadata sheet. As I described above, new projects are provisioned, SCC populates this metadata sheet (via cell-links) with applicable intake metadata (e.g. Project ID, Project Name, Region, Business Unit, Location, Sponsor etc.) and project metadata that (e.g. Start Date, Finish Date, Financials, Statuses etc.).

As it stands, this portfolio level Reporting Metadata sheet has 75 columns which (based on a ceiling of 25,000 cell-links) will only allow us to provision 333 projects before Smartsheet Control Center stops populating the sheet. As Smartsheet or Control Center does not provide an archive function, we will reach 333 projects sooner rather than later.

From previous experience, SCC is nowhere near mature enough for me to have any level of confidence that I could "Update Reporting" for broken projects successfully. We hit the 5,000 soft limit fairly early on (I was not happy to discover this in a live environment) and even after it was removed, I had to spend hours manually re-creating the links. We were forced to take the manual approach after unsuccessfully trying all the recommended steps within SCC (based on advice from your Product Team) to re-establish the cell-links.

Given that the solution was configured/developed by Smartsheet under a professional services agreement, it's disappointing that we have to discover these sorts of restrictions ourselves. At no stage were we informed about the 5,000 cell-link soft limits or 25,000 cell-link hard limits during or after the implementation. This should form part of the Smartsheet best-practice approach to implementing SCC solutions and related information should be readily accessible to customers. I'm still waiting for a user manual for SCC 12 months on, but in the meanwhile, your Product Team has spent the time adding Personal Profiles and messing about with colours.

As Smartsheet were aware of our requirements and the limitation within the platform before, during and after our engagement, I should not have to "find out the hard way" by experiencing data loss, being roadblocked, reading support articles or posting on this forum. While communications and expectations gaps occur, this seems to be the norm rather than the exception and it's frustrating me no end.

Josh Garcia

Just encountered the exact same limit. Would have also taken a completely different approach had I known the cross-sheet reference limit was only 100.

I'm using them to roll up project metrics to a program and portfolio level. I've got less than 100 project sheets to roll up, with a handful of metrics each. A limit of 150 would have been more than enough for my use case. 100 came in just under.

Going to test an approach where I split these cross-reference formulas across different sheets and cell-link them back into my rollup, wish me luck!

Josh

Josh Garcia

So funny story - if you copy a project sheet, delete all of the cross-sheet references, and try to make new ones, the 100 limit cap still applies. Tried deleting formulas, saving as a template, and create a new sheet from the template. No joy. Sounds like I'm making a new sheet from scratch for this workaround attempt. :-/

 

Josh

Josh Garcia

It worked!

To keep the column layout in a secondary sheet, copy a blank row to a blank template and remove unneeded columns. Create the cross-sheet formulas on the second sheet. Add cell-links to the first sheet to pull in values from the second.

Chris McKay

Thanks Josh,

I spent 4 hours re-doing my structure and managed to get the sheet to work with fewer cross-sheet references.

As you've pointed out, I would have employed this (less useful) tact if I'd known about the 100 cross-sheet reference limit before beginning. The fact that you needed to hack and slash your sheet in the way you've described (new template, duplicate sheets etc.) is terrible.

Still not very impressed with the limits or the fact that the support article has not been updated to highlight the 100 cross-sheet reference limitation. It can't take more than 5 minutes to amend the article to include:

• Please note: A maximum of 100 cross-sheet references can be created for each sheet. Duplicate references using the same range elsewhere on the sheet will not count towards this limitation. If you require more cross-heat references, please consider using cell-linking as an alternative.

Daniel Stein

Josh,

If you want to create a new sheet without any of your cross-sheet references, you could uncheck the "Cell links and cross-sheet references" checkbox in the "Save as New" dialog, under "Data & Formatting Options." This will immediately remove all cell links and cross-sheet references in the new sheet.

If you manually delete cross-sheet formulas, the underlying references are cleaned up after 2 hours (after someone edits one of the sheets). This is to prevent cross-sheet references from being cleared while users are in the midst of editing their sheets.

We also recently released a "Delete" control on the source side of cross-sheet references. You'll see it in the tooltip of the source cells and can use it to clear all references from another sheet.

Chris,

Thanks very much for taking the time to give an overview of your SCC implementation and also for the reminder about the documentation update. The documentation update is indeed in our queue. Our support team reviews requested updates to content on a regular basis to ensure consistency across all our articles, so it should get in there relatively soon.

I'm sorry to hear about some of the troubles with your SCC implementation and would encourage you to continue to work with your customer success or professional services contact.

Thanks,

Daniel

Josh Garcia

Daniel - good stuff, thanks for the tips!

Josh

jrobbins

And I just wasted soooo many hours setting up a data roll up sheet to use for dashboards, only to run into this exact same problem.  I'm so tired of running into functionality problems with this software.  Perfectly logical stuff is just missing in multiple areas of this program.  It's like you guys had a shoe string budget and only developed things halfway.  Either that or you just enjoy coming up with "workarounds".  Now I must begin the search for new software.  I'm done trying to make this thing work.

Adam Hutchinson

O.k. - so I did a freak out about the 25,000 limit - referencing and analyzing data is critical for what I am building...

But then realized that is only 25K per sheet.  

So, split referenced data across multiple sheets..... I now have 32,000 cells referenced in two sheets. 

P_Youderian

Hey Daniel,

May I please suggest that you add,

"If you use the same reference more than once, e.g., as a lookup table, it only counts once against the limit."

Or some other explanation about how these limitations are calculated to the support article.

 

Also, just to clarify:

Source sheet: 

• Is there a limit to the number of documents that can pull references from this sheet?
• Is there a limit to the total number of references that can be pulled from a sheet?

Formula sheet:

• You can use up to 100 distinctly named cross-sheet references.
• Those references can pull from no more than 25,000 cells in total.

John Harder

I hit the 100 cross-reference limit and split my file into two, leaving one file with close to 100 cross-references and the other with only about a dozen.  The file with only a dozen cross-references still provided the error when I tried to add a cross-reference.  A saw the 2-hour note and waited over night.  Same error.  I also saw a note on saving as new, where this would deleted cross-references; however, I still received the same error.  So starting from scratch for the second half of the file.

John Harder

I misread the save as new comment.  I followed the instructions, where this removes all of the cross-references, which is not what I wanted.  I was looking for a way for the deleted cross-references to be removed, which is what I thought waiting two hours would do.  I appreciate your feedback.  Thank you.

McneilOMC

This is very frustrating , first i hit the 5000 row limit , now the 25000 cell reference limit, this after spending a decent amount of time on this work. (Friday night tough one had to redo my sheet to work around the 25000 cell limit.

Worried what else i will run into

SS - Are these limit expandable or are we stuck with these limits , my worry is they cannot increase these limits as the product is not capable of this , Please say i am wrong

 

Corbin Neilson

....Imagine purchasing a vehicle, driving it 60 mph for a year, and then receiving a message on the dashboard that the car has a limit of 50 mph....

We integrate our teams' JIRA backlogs into Smartsheet and produce 2 reports for Executive Management. The first report uses JIRA time tracking fields to breakdown labor costs and project profitability against scopes of work; the report was in place for about a year. 

The latest one utilizes the Original Time estimate to project our needs for resource allocation and compare to actual; this report was in place for ~2-3 weeks.

After engineering these reports we rolled them out to Executive Management. These reports are critical. 2 days ago, both reports were wiped out and I received the 25,000 cell limit message. Although we were well over the 25,000 limit at the time of report creation, we were never notified by the system, not limited by the system, nor provided an option to pay more money. 

So when we want to get to work on time, Smartsheet sits in the garage while we use a tool that can move us more efficiently.