Cross-sheet reference limited to under 50,000 references

EmilyH

Excited about the increased cross-sheet reference limit to 100,000, I jumped on the bandwagon and started to replaced the Destination sheet LEFT(SUBSTITUTE(JOIN formulas with the Index/Match formulas using cross-sheet references. In this workflow, we are pulling data from 5 Source sheets into one “Central (destination) sheet”. I LOVE the increase but I have already maxed out at 45,835 cross-sheet references. I even removed all source sheet references and re-referenced (twice) to make sure there were no hidden or missed references. 

Even though I am very excited about the increased limit, I had to revert back to using the LEFT(SUBSTITUTE(JOIN formulas for seven columns. Which resulted in mixed emotions about the update.

Here are the cross-reference stats for my Destination sheet:

Sheet 1 = 1577 rows on source sheet, Destination sheet references 19 columns = TOTAL 29,963 references

Sheet 2 = 73 rows on source sheet, Destination sheet references 25 columns = TOTAL 1,825 references

Sheet 3 = 314 rows on source sheet, Destination sheet references 30 columns = TOTAL 9,420 references

Sheet 4 = 1 rows on source sheet, Destination sheet references 27 columns = TOTAL 27 references

Sheet 5 = 345 rows on source sheet, Destination sheet references 12 columns = TOTAL 4,140 references

Linked Cells in Destination sheet = 460 cells

Total cell references = 45,835 cells

Should I log a ticket?

Thank you,

Emily

Paul Newcome

What is the error you are getting and how exactly are you creating these references?

There is still the limit to how many unique cross sheet references, so if you have each of the columns coming from each of the sheets as their own references, that means you have 113 unique ranges. I am still looking for confirmation, but I don't believe this was updated beyond the original 100.

EmilyH

Hi @Paul Newcome,

You are correct, I am referencing 113 columns in total. So far. I am hoping to reference 8 more source sheet columns from sheet 1 (1577 rows on source sheet).

I am receiving error message: Unable to create cross-sheet reference, because this sheet already contains the maximum number of different cross-sheet references.

I thought this error was due to the number of references but, I guess it means I have maxed-out at the number of column references, being 100. In this case, 113 columns.

Is that correct?

If yes, I could include multiple columns within one cross-sheet reference and use a V-Lookup formula to get the select column data?

Thanks.

Emily

Paul Newcome

It isn't necessarily the number of columns being referenced, but the number of ranges themselves.

{Range 1}

{Range 2}

{Range 3}

This counts as 3 unique cross sheet references regardless of what those ranges cover whether it be a row, a single column, multiple columns, specific cells, etc.

If you are not going to exceed the number of cells and you are using INDEX functions, you can use one range to reference multiple columns and use the 3rd portion of the INDEX function to specify which column to pull from.

The error you are getting:

"maximum number of different cross-sheet references."

is different from the maximum number of cells. I hope that makes sense.

EmilyH

Yes! Thank you. I updated the cross-sheet Index/Match formulas and included more columns. It works and I was able to successfully reference all source data.

Thank you for talking me through this issue!

Emily

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "Helpful". This way other people searching for a similar solution can know that one may be found here.

Melisa Dannhauser

@Paul Newcome I have a question. If I have a range that I reference and the range is one column with 1,400 rows. If I reference that same range in a sheet 100 times, am I at 140,000 cross sheet references?

I have a summary sheet that references only 5 different ranges but each range has 1,400 rows. I reference those ranges many times and I keep getting the error that the 100k cross-sheet reference limit has been reached... I'm trying to understand the error so I can re-work my sheet, I even had a one-on-one session with Smartsheet pro desk and all I hear is it can't be done and I need to split my source data up (not an option). Nobody is trying to help me understand how the references are counted so that I can re-work my formulas and make the sheet functional.

Any input is appreciated.

Paul Newcome

@Melisa Dannhauser There are a few different limits that you will need to keep in mind.

Assuming we are working on a single metrics sheet that is looking at a single reference sheet, referencing a single column with 1,400 rows as a single range will count as 1,400 cells and 1 unique cross sheet reference regardless of how many times you use it on the metrics sheet.

If you have 5 different ranges with 1,400 rows each, it will count as 5 unique cross sheet references and 7,000 cells regardless of how many times they are used in the metrics sheet.

Do you have cross sheet references to other sheets on this one sheet you are getting the error on? If you right click on any cell within the sheet, you can select "Manage References" and get a list of all cross sheet references as well as cell links in the sheet. It may be that cleaning some of those up will help.

If all you are doing is referencing those 5 ranges/7,000 cells, you should not be getting any limit warnings.