Help counting cross-sheet formulas, references, cells

Melisa Dannhauser

I have a master sheet with 1,400+ rows (and growing each day).

I have a summary sheet where I cross reference entire columns from the master sheet. In the summary sheet, I have a formula with multiple conditions that references 5 different columns from the master sheet (aka I have 5 different cross-reference ranges). But this formula is repeated 50+ times on the summary sheet (just includes different conditions).

I receive the below error all the time and my formula cells won't update any more. I have talked to SS support but I'm still having issues figuring out the source of the problem. What does it mean that I'm referencing more cells than max allowed (100,000)? Is it counting the 5 columns times the 1,400 rows, times the 50 times used??

I thought a cross-reference can be used many times and it counts as one so I definitely didn't exceed the cross-reference limit, I only have 5 references total. But this is talking about cells which is so confusing... Any help appreciated. I have been trying to re-work my summary sheet in a million different ways but I just can't get around this error and I don't know what the root cause

is!

Paul Newcome

I believe we may be discussing this on another thread?

It should only be counting the 5 columns times the 1,400 rows once regardless of how many times the reference(s) are used within your sheet.

Do you have cross sheet references coming in from other sheets? You can right click on any cell within the sheet (where you are using the cross sheet references) and select "Manage References" to get a comprehensive list of all cross sheet references and cell links.

The Source column will tell you where the reference is coming from. If you hover over one of the rows, three vertical dots will appear to the right. Clicking on that will give you the option to edit, duplicate, or delete the reference.

I would start by going here and making sure to clean up any old references.

Melisa Dannhauser

Ok, so the reference page says I have 13 references. This is pretty much my sheet, it's not big. I have a few more individual cells that have other references but each one of these in the table has the same 5 references. I don't get what the error means... Support told me it's because the master sheet has so many rows but it makes no sense...

Paul Newcome

Ok. Go through all of your references. In your original post you say you only have 5, but the Reference Manager says 13. That's 8 more references than you had originally mentioned. Depending on what those ranges are referencing, it could be very possible that one of them is causing the issue. You can hover over it, click on the three dots, then select "Edit". This will allow you to view the range. If you like what you see then you can just cancel the edit, but if it is wrong then you can either adjust it and save or you can just delete the whole reference.

Melisa Dannhauser

13 actually makes sense because I forgot I was also pulling in Project names and other project details to the sheet from a different sheet. All of those other master sheets are 200 rows max, the larges one I have is the initial master sheet of 1,400 rows.

I guess I'm still trying to figure out WHAT the issue is, I mean even if it's 13 references from the large sheet, that's 13*1,400 rows, which is nowhere near 100,000...

Support told me that it counts the 13*1,400 times the number of times I use each reference in formulas which is 50-ish... But like you said (and like I've read online), it shouldn't be doing that. I'm just confused as to what the issue is...

Melisa Dannhauser

I just realized I used an index/match function for these instead of my typical sum/collect. I referenced a whole range including ALL columns and all rows... maybe that's the culprit... I will re-do this and see if that helps...

Melisa Dannhauser

But I guess my idea will make it worse. Right now I have ONE reference (index match) that collects 22 columns with just that one formula/reference. If I change it to sum/collect, it will have 22 different references, one per column...

So I guess my index/match reference that goes over the whole range is for 22 columns and 1,400 rows. Is that the issue you think?

Paul Newcome

There you go. That may be it. For future reference... INDEX/MATCH only requires 2 columns. It isn't like a VLOOKUP where you have to select the entire set of columns and then choose a column number.