The 25,000 cell reference limit is puzzling

Emma R

@Paul NewcomeYes I am using that now and having the same issue. This must be some kind of error. I have one formula where the VLOOKUP and INDEX/MATCH gave me the same result and worked fine. Then I copied them to change the output to something else and before I could, I got the pop up telling me some of my references are over 25k. I did not change references. Then I decided to just rewrite them instead of copying and I get the pop up saying to select less than 25k cells when choosing the same reference as the first one. So it only works some times? In all instances my references were less than 25k so it still does not make sense to me. Unfortunately INDEX/MATCH has not helped.

Paul Newcome

@Emma RSometimes references are held even though they are not being used anymore. How many different cross sheet references do you need? If it is only a few, you can go to the source sheet and delete ALL references to the target sheet, then go back to your target sheet and rebuild only the references you need.

The limit is not necessarily 25,000 per range, but 25,000 TOTAL. So even if none of your ranges are that large on their own, multiple ranges could add up quickly.

There are some workarounds available depending on your exact needs and setup as well such as joining some or all of the reference data on the source sheet into a string for each row, pulling only that string column over to your target sheet, then parsing it out onto your target sheet.

SRenner

@Paul NewcomePaul, Im running into the issue shown below. I have 11 columns that Im referencing with a total of 74,041 cells.

I am however using the same column (same range) on multiple cell formulas. does each range only count once or each time you use it?

for example i am pulling the store count range and date range into 6 different formulas referencing from the same sheet. does the store count range being reference only count 1 time or all 6 times towards the 100,000 limit?

Paul Newcome

@SRennerIt only counts once no matter how many times you use it. So using the same range 15 times counts as only 1 unique cross sheet reference, and if that range consists of 1,000 cell it only counts as 1,000 cells and not 15,000.

This should help clear things up... The specific verbiage Smartsheet provides regarding the 100,000 is...

"Uniquecells referenced in cross-sheet formulas"

Key word being "Unique".

Paul Newcome

@SRennerI meant to ask in m last comment... What exactly is the issue you are running in to?

SRenner

Thanks.@Paul Newcome

The last column i am formulating is saying that i have more than 100,000 unique cells referenced. the math doesnt add up. i have done it twice now. i may start with two new sheets and see if that fixes it. i know the formulas get stuck in the sheet sometimes. I have deleted the cross reference everytime i started over and get the same result. not sure what the deal is. and i am using unique ranges that do not equal over 100000. ill let you know when i do the new sheets. thanks Scott

Paul Newcome

Ok. Hopefully new sheets will do the trick.