100,000 cell reference limit - do I understand it right and can I monitor it?

Rob Wright · 04/23/21

I have recently come across the limitation of smartsheet whereby you can only have 100,000 cell references, when I tried to add a vlookup where the table array was 140 columns wide, to a sheet that was already using several vlookus.

My first question is does the 100,000 cell limit mean that if for example I had 100 links to cells in other sheets, and two lookups that both referred to arrays in other sheets that were 200 rows deep and 50 columns wide, that I would have used up 100 + (200x50) + (200x50) = 20,100 cells references, and I would have 79,900 left to use in that particular sheet?

My second question is whether or not there is a metric anywhere which tells me how any cell references I have used, so that I know whether or not my next vlookup will work or not?

Any help appreciated.

Paul Newcome · 04/23/21

You are correct in your understanding, and there is currently no way to track it other than manually.

You can however convert your VLOOKUPS over to INDEX/MATCH formulas which could potentially save A LOT of references.

INDEX/MATCH only references the two target columns. The one to pull and the one to match on. You don't have to reference the other 138 columns in between. Plus since you are referencing the two columns separately, they can be moved around and it doesn't matter which is on the left and which is on the right within the source sheet.

=VLOOKUP([Column Name]@row, {Range of 140 Columns}, number, false)

=INDEX({1 Column To Pull From}, MATCH([Column Name]@row, {1 Column To Match On}, 0))

The breakdown:

INDEX pulls from a column based on a specified row number:

=INDEX(column_to_pull_from, row_number_to_pull)

MATCH outputs a row number based on where within a column the data being searched for is found.

=MATCH(value_to_match, column_to_find_match_in, 0)

So we use the MATCH to output the row number where the match was found and use that automated number to tell the INDEX function which row to pull from.

It is more flexible, more efficient, and requires less cells to be referenced since we don't need to include all of the stuff in the middle.

Rob Wright · 04/26/21

Thanks Paul,

This was really helpful; I am relatively new to smartsheet and only a mid level Excel user but the job I jae just started required smartsheet experience which I am gaining fast and some excel know how to build solutions for my team.

I was aware of index and match years ago but never had the need to use it. I can see now that wit smartsheets references limitation that Index and match is going to be a very valuable tool!

Paul Newcome · 04/26/21

Happy to help. 👍️

Ulrike Schmidt · 09/19/21

Genius, I just came across the same issue, wonderfully explained @Paul Newcome !!

Aurel · 04/08/22

Hello @Paul Newcome ! I read now the history and I am facing the same issue : reference limit.

My problem is that my company is so big, that even with the index solution, the number of column and rows involved reach the 100 000 already. I try to optimize, but we are growing, and every time I see my options reduced for optimizing and cleaning columns to not reference.

I understand that the limit of 100000 references include and Index and Vlookup references.

Is there any potential improvement of this limit in the future ? or alternative solutions ?

Andrée Starå · 04/08/22

Hi @Aurel

I hope you're well and safe!

When I hit the limits in a client solution, I usually use JOIN to collect multiple columns in the Source sheet and split them out in the Destination sheet.

Make sense?

Would that work/help?

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome · 04/11/22

@Aurel I do the same that Andree has mentioned sometimes. I will join the columns I want to reference into a single cell and then pull this "joined" column over to the other sheet.

Other options could be premium apps Data Shuttle and/or Data Mesh.

Aurel · 04/12/22

Hello @Andrée Starå and @Paul Newcome,

Thanks for your reply and solution ! I understand how JOIN could optimize, but unfortunately, it will not cover all our needs.

I already thought and talked about Data mesh to my manager, this is maybe the time to look further and invest in it on our side. I also think this is the best solution.

@Paul Newcome Is there by any chance possible updates by Smartsheet for increasing this reference limit in the future?

Have a nice day !

Paul Newcome · 04/12/22

@Aurel I haven't heard anything about there being any increases on the roadmap, but Smartsheet tends to keep their roadmap pretty private.

Andrée Starå · 04/12/22

@Aurel

Happy to help!

Data Mesh 👍️

Hopefully, regarding future limitations, I expect that they will increase sooner than later.

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jeff Reisman · 04/12/22

@Aurel What I have done to get around the 100,000 cell reference limit is to create one or more sub-sheets that are pulling data from different columns in the source sheet. Then on my destination sheet, I use cell links against the sub-sheets, since the sub-sheets only contain a sub-set of the data from the source sheet.

To illustrate one of my uses of this solution:

I have to pull data for anywhere from 100-500 rows and 20+ columns to send product updates to our partner company each month. The source is a sheet with about 90 columns and 3500 rows. On my data lookup sheet I enter my 100-500 product numbers. I use INDEX/MATCH to pull values for about 8 columns.

On subsheet 1, I use cell links to pull the product numbers in from the data lookup sheet. Then I use INDEX/MATCH to gather another 8 or so columns of data.

I do the same thing on subsheet 2.

Back on my data lookup sheet, I use cell links against the subsheets and collect the two sets of 8 columns from each subsheet.

So each month, I just remove whatever product numbers were on the data lookup sheet, Save, and then enter the ones I need to lookup for this month, save. The formula columns update immediately, and within 10-20 seconds the cell-link columns flash blue and update as well. Lastly, I select all my populated rows in the data lookup sheet, select Copy to Another Sheet, and select the sheet our partner company has access to view.

Francisco · 09/26/23

Currently using index match and hit the limit to day - incredibly frustrating; going to have to re-work the structure or abandon SmartSheet for this use case. Really unfortunate in a time crunch.

Francisco · 09/26/23

https://community.smartsheet.com/discussion/comment/325900#Comment_325900

This seems like such an annoying work around!

Stefan · 09/27/23

https://community.smartsheet.com/discussion/comment/325891#Comment_325891

@Andrée Starå , @Jeff Reisman

Hi, from Engage I understood, that Smartsheet as first step will raise the limit to 500.000 links early next year. Next summer (northern hemisphere) or so Smartsheet intends to raise the limits to 5.000.000!

Until then we will need to use workarounds like Jeff described.🤷‍♂️

At least the improvement to the infrastructure is under way and we have an ETA :)

Greetings

Stefan

Jeff Reisman · 09/27/23

https://community.smartsheet.com/discussion/comment/397183#Comment_397183

I hadn't heard about that! That's fantastic news. Just the other day as I was building a weekly help ticket review sheet, I found myself adding up cell links to be sure I wasn't getting too close to the limit!

100,000 cell reference limit - do I understand it right and can I monitor it?

Answers

Categories