Cross Sheet Formula Limit Error

Matt Metzger

I'm currently getting an error related to the cross-sheet formula limit. I'm not sure why I'm getting the error. So far I have referenced 6 columns in other sheets with SUMIFS formula. I received the error when I added the 2019 formula below. There are ~17,000 records on the referenced sheet, would that be causing the problem? I need to finish 2019 and 2020 for the references, for 2020 I'll need to add an additional column. I would think that I'll only be up to 10 column references from other sheets, that should cause a problem, right? Thanks for the help!

=SUMIFS({2018_amt}, {2018_acct}, Account@row, {2018_subacct}, Subacct@row)

=SUMIFS({2019_amt}, {2019_acct}, Account@row, {2019_subacct}, Subacct@row)

Paul Newcome

The problem then would be that you are referencing too many cells. You are limited to 100,000 referenced cells. If you have approximately 16,650 rows in your reference sheet, you will only be able to reference 6 columns. Attempting to reference a 7th column would mean you are trying to reference 116,550 cells which is over the limit.

You will want to find a way to consolidate the columns on the source sheet and pull it over to your target sheet. There are quite a few different options for this.

One option would be to use a JOIN function on the source sheet and then use cell linking or an INDEX/MATCH to pull this column over to your target sheet where you can parse it out and reference the data that way.

Paul Newcome

What is the exact error you are receiving?

Matt Metzger

You can only reference a total of 100000 cells from other sheets.

Paul Newcome

The problem then would be that you are referencing too many cells. You are limited to 100,000 referenced cells. If you have approximately 16,650 rows in your reference sheet, you will only be able to reference 6 columns. Attempting to reference a 7th column would mean you are trying to reference 116,550 cells which is over the limit.

You will want to find a way to consolidate the columns on the source sheet and pull it over to your target sheet. There are quite a few different options for this.

One option would be to use a JOIN function on the source sheet and then use cell linking or an INDEX/MATCH to pull this column over to your target sheet where you can parse it out and reference the data that way.

Frank Malinowski

Is the 100,000 referenced cells limit per formula or total across all the formulas in all of my Smartsheets?

Paul Newcome

@Frank Malinowski It is all formulas within a single sheet. I also believe this limit has been increased substantially.

soakley

It hasn't been increased enough.....

Would converting the columns with formulas to values only, help?

Paul Newcome

@soakley Your particular error is coming specifically from cross sheet references. If you are using VLOOKUP, I suggest replacing with INDEX/MATCH. If you are not, then you will need to use a helper column on the source sheet to join each of the cells together on all of the rows, use a formula to bring the strings over, then reference the strings on the destination sheet.

Chris Kaliveas

@Paul Newcome

Hey Paul,

I was wondering if you would happen to know the answer to my questions below.

1. If I have 100 rows in a sheet and I use two unique cross-sheet references in one column formula (Column 1) and then have another column formula (Column 2) using the same two unique cross-sheet reference as (Column 1), how many cells are used against the 100,000 cell limit?
2. If I have 100 rows in a sheet and use a summary field using two unique cross-sheet references which are not used in any column or cell formulas, and I then use the summary field in a column formula, how many cells are used against the 100,000 limit?

Kind Regards,

-Chris

Paul Newcome

@Chris Kaliveas

Each unique cross sheet reference will count as a single reference to that number of cells, so really it depends on how many cells are being referenced by your cross sheet references. Duplicating those references (using the same formula on multiple rows in the target sheet) does not impact the number of cells referenced.

So if your reference sheet contains 1,000 rows and you are referencing two columns, then you have used 2,000 cells towards the 100,000 limit. Using these cross sheet references again in another formula does not increase the count of cells referenced.

The answer to your second question is the same as the first because of how the cross sheet references work.

This is one of the reasons INDEX/MATCH is generally a better idea than a VLOOKUP. With INDEX/MATCH you are only referencing the two columns that you actually want to use whereas with a VLOOKUP you are referencing every column in between. Lets say your reference sheet is 10 columns wide and 100 rows long. Your column to match on is the first column and your column to pull from is the last column. This means that for a VLOOKUP you have to reference 1,000 cells even though really you only need 200 of those whereas with the INDEX/MATCH only referencing the columns you actually need, you are only referencing those 200 cells that you actually need and not those 800 cells that are in between.

.

However... There is a 25,000,000 cell reference limit on the same sheet. Lets say we are only working in Sheet A and have 100 rows. If we use a formula such as this on every row:

=ColumnA@row + ColumnB@row

then we are referencing 200 cells towards that 25,000,000 (2 on each row times 100 rows).

But then we are also using a setup to output the row number on every row which involves referencing an entire column:

=MATCH(Auto@row, Auto:Auto, 0)

Now we are referencing 100 cell just with the "Auto@row" portion, but then we reference 100 cell (Auto:Auto) 100 times by putting it on every row. Now our count is 10,100.

Chris Kaliveas

@Paul Newcome

Hey Paul, thank you heaps for explaining the cell and cross-sheet reference limits as it is sometimes difficult to understand the Smartsheet documentation online. This is a big thank you again for your help as your input plays a big part in designing a solution that works without a surprise down the road. We are building a reporting solution to import data from a source system and then display this data to end users with summarised metrics. In short, and due to the cross-sheet limits dealing with lots of records, we will leverage data shuttle to update a master metric sheet based on 4 other summary metric sheets which each source data from 3 other sheets each (total of 12 sheets across the 4 summary metrics). We are in testing phase with this solution so keeping our fingers crossed :-)

Kind Regards,

Chris

SS_New

@Paul Newcome

I am struggling with 100,000 reference issue with my sheets. I see above that you have suggested to use JOIN function at the source and split it at the destination. I tried doing it by was not able to get to work. Any help in this regard will be highly appreciated.

Thanks in advance.

Unknown

This content has been removed.

Paul Newcome

@SS_New What part exactly are you needing help with?