Remove or Increase Cross Sheet Reference Limitations

Courtney Collier

Please consider removing or increasing cross-sheet reference limitations. I recently built a solution that would require me to manage 3-4 sheets each month for monthly audit results tracking from Joint Commission. I spent weeks building and testing and at the end had to add more questions (total of about 125 to the form. Two days ago, I was adding the new info and cross-references for metrics and I reached the limit. I spent the last two days working non-stop to restructure the sheets to bi-pass the limits (on a holiday weekend). Now, I have approx 15 sheets to manage and still not sure I can make it past a couple of months for metrics/reporting. It is also important that I maintain past months/year's data for Joint Commission for each clinic (approx 125) - I created regional scorecards listing each clinic's results. The limitations just made managing the build much more inefficient and difficult and I am still worried I will reach the max soon, breaking everything again for a rebuild. This is not scalable. Thanks for your consideration.

mkilroy

As a work around to the 100 cross-sheet references limit, I would like to propose that a cross-sheet reference can include multiple columns, but then you have the ability to select which of those columns you want to use in your formula.

To provide some context, I’ve created Capacity and Allocation (C/A) templates for 12 functional groups. In each template, the functional managers will enter in who is available to work on ‘x’ Project. I’m attempting to pull that information in from each C/A template using SUMIFS formulas. However, since each month, quarter, and half year requires a cross-sheet reference (about 20 total, including the Department and Project ID cross-sheet references), I was only able to pull in data for 5 of the 12 functional teams.

The formulas look something like this:

=SUMIFS({CA - PPM Jan 23}, {CA - PPM Department}, Department@row, {CA - PPM Project ID}, [Project ID]@row)

=SUMIFS({CA - PPM Feb 23}, {CA - PPM Department}, Department@row, {CA - PPM Project ID}, [Project ID]@row)

=SUMIFS({CA - PPM Mar 23}, {CA - PPM Department}, Department@row, {CA - PPM Project ID}, [Project ID]@row)

I’m curious if there is a way to reference the entire timeline (Jan 23 to H2 25) in a cross-sheet reference, but only sum the numbers from a specific month.

Thank you!

EmilyL

I love Smartsheet. I think it is a wonderful program.

In using Smartsheet for a couple of large organizations that have a high volume of transactions, etc., however, I have run up against the limits of Smartsheet so much that it comes close to negating all of the benefits.

I am sure I am not the only one who would appreciate the following limits be removed (I don’t know if it’s server space or what needs to be done):

1. 500,000 cell limit for a sheet
2. 100,000 cells referenced in a sheet (this comes up a LOT and to deal with it I end up making 20+ extra sheets to try to recombine data for reports that shouldn’t be necessary)
3. report limits—I’m less familiar with these but something like 2500 rows (large organizations have a lot of data)

And then the other big limitation is the processing time. When dealing with larger amounts of data it takes be 3-4 times as long as it should to complete tasks because every time I have to open a sheet or reference another sheet I have to wait for several minutes. It takes me less time to do the work in the sheet than it does for the sheet to open most of the time (and this is when I’m creating them)

Again, Smartsheet is a wonderful tool. It is my most frequent choice when I’m working with a company to organize their data and set up workflows, etc.. These are just things that would absolutely change things for the better.

Swicker

I absolutely ditto this! If Smartsheet wants to be used in large companies for wide-scale solutions - it will be critical to expand their cell limitations and speed up the processing time.

Riaan Kotze

Hi guys, kindly look into it to remove the cross-referencing limitation. I have to build a report over 12 months, reporting on each month, with quarterly reports as well as YT reports and the cross referencing is completely breaking my vibe..lol

Please I love SS and would really like to build this report on the platform.

Best regards,

Swicker

Adding my support for this improvement! Generally increasing the capacity of sheets to reference other sheets/reports/dashboards, etc. would greatly improve my ability to develop company wide Smartsheet solutions.

FeFagoni

I've a employee weekly survey running over SmartSheet Sheets, it's being very tough to create new data/KPIs as there is a hard limitation of Cross Reference Sheet, I suggest to at least double current number

Jessica Limke

I am currently trying to pull in data from multiple different sheets into 1 summary view sheet and hit the 100 unique cross-reference max limit, and I am not even halfway finished with my task. Can this limit please be removed or at the very least increased to a much larger number?

Paul Newcome

@Jessica Limke You can use a helper column in the source sheet to combine the multiple columns across each row into a single string (with a unique delimiter), pull this string over to the target sheet, then use a parsing solution to pull the individual data pieces out of the combined string. This will also help with sheet performance since cross sheet references can have a big impact on that.

Jubial

Second this - I've had many sheets completely break on me due to this. :(

RolandoN

I'd also like this.

Becky Sandoval

Please Remove Cross-Sheet Reference Limit.

Roxanne

@Paul Newcome that sounds like a solution that could work for me as well, can you please give me an example, I get that I add an extra row, but please unpack the parsing solution

Paul Newcome

@Roxanne There are a number of different ways to parse in Smartsheet in other threads. The right one for you depends on exactly how your string is created. My personal preference when I have total control over all of the variables is to use different characters as delimiters that wouldn't be found anywhere in the strings. I like to use the UNICHAR function because I can use a set of numbers within the function to output unique delimiters but also keep the delimiters in numerical order to make adjusting formulas easier. Since the helper column can be hidden after setting it up, it doesn't really matter what it looks like as long as it is functional.

=UNICHAR(8600) + [1st Column]@row + UNICHAR(8601) + [2nd Column]@row + UNICHAR(8602) + [3rd Column]@row + UNNICHAR(8603)

Once you bring this string over, you can use a formula to parse things out as needed.

=MID([Helper Column]@row, FIND(UNICHAR(8600), [Helper Column]@row) + 1, FIND(UNICHAR(8601), [Helper Column]@row) - (FIND(UNICHAR(8600), [Helper Column]@row) + 1))

=MID([Helper Column]@row, FIND(delimiter for start of piece you want to grab from string, [Helper Column]@row) + 1, FIND(delimiter for end of piece you want to grab from string, [Helper Column]@row) - (FIND(delimiter for start of piece you want to grab from string, [Helper Column]@row) + 1))

FeFagoni

As user I would like to be able to at least have 200 distinct cross-sheet references