Cross reference cell limitation of 100,000

JBG

Dear Smartsheet community, sorry for the lengthy message, but i wanted to be clear on what i am looking for.

I have come across a lot of articles regarding the 100,000 record limit that you could reach with cross referencing between sheets. Nowhere I could see a clear formula to actually calculate this. Below is a formula I created based on my understanding of the calculation:

The following explanation on the sheets:

Destination sheet = the sheet in which I have formulas with references to other sheets

Source sheet = the sheet that is being referenced by my destination sheet

Nr of references =

Destination sheet # of records * nr of active references in my destination sheet +

Source sheet1 * # of unique columns being referenced +

Source sheet2 * # of unique columns being referenced +

Source sheet3 * # of unique columns being referenced

As an example I have a sheet called “timesheet details” (my destination sheet). In this sheet I have columns that are gathering data from other sheets to format the sheet for consumption. I have three other sheets called “Absence”, “Overtime” and “Employees” (my source sheets). These sheets contain the data being referenced by my “timesheet details” sheet.

In the “timesheet master” sheet (destination sheet) I have 1000 rows with 40 unique references (based on the "manage refernces" option). I only use index/match to obtain data, no VLookup. Growth in this sheet is 30 records per new employee. See growth of employees below.

In the “Absence sheet” I have 300 records (growing by 150 a year) and there are 15 references from the destination sheet.

In the “Overtime sheet” I have 150 records (growing by 75 a year) and there are 15 references from the destination sheet.

In the “Employee sheet” I have 30 records (growing by 2 a year) and there are 10 references from the destination sheet.

If my formula above is correct I would have the following result, with the current amount of data described above:

1000*40 + 300*15 + 150*15 + 30*10 = 47,350

Based on this result and the growth per sheet, we should have about 10 years before reaching the limit, correct? Obviously a spike in any of the sheets will cause a deviation of the projected timeframe.

		Dest sheet	Absence	Overtime	Employees
GROWTH		30	150	75	2
References		40	15	15	10
# rows		1.000	300	150	30
Sum	47,350	40.000	4.500	2.250	300
	Total * references	Previous+Growth
Year 1	52.845	1.060	450	225	32
Year 2	58.640	1.120	600	300	34
Year 3	64.435	1.180	750	375	36
Year 4	70.230	1.240	900	450	38
Year 5	76.025	1.300	1.050	525	40
Year 6	81.820	1.360	1.200	600	42
Year 7	87.615	1.420	1.350	675	44
Year 8	93.410	1.480	1.500	750	46
Year 9	99.205	1.540	1.650	825	48

MichaelTCA

@JBG

Hello,

The sheet can hold 100,000 cross sheet references. You can have more than 1 reference in a single cell, but only references pulling data to the target sheet count towards that limit.

For example:

1000 rows, 10 columns, with 10 references in each cell. You have hit the limit.

JBG

@MichaelTCA

Thank you for your reply. If I understand you correctly then my formula of

Nr of references = Destination sheet # of records * nr of active references in my destination sheet +Source sheet1 # of records * # of unique columns being referenced + Source sheet2 # of records * # of unique columns being referenced + Source sheet3 # of records * # of unique columns being referenced

Should be changed to

Nr of references = Source sheet1 # of records * # of unique columns being referenced + Source sheet2 # of records * # of unique columns being referenced +Source sheet3 # of records * # of unique columns being referenced

MichaelTCA

@JBG

I don't understand where you are going with the unique columns. The cross references have nothing to do with columns except selecting a range.

Nr of references = summation of (all source sheet records contained within the selected range * quantity of references contained within each cell) from 0 to 100000.

You can have 3 references in a single cell and 20 in another. There's no linear formula.

If you wanted to, you could have 100,000 references in a single cell.

MichaelTCA

@JBG

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

MichaelTCA

@JBG

Keep in mind, as you add data to the range within the source sheet, every cell that already uses that range as a reference increases too.

JBG

@MichaelTCA

When I am talking about unique columns, I am referring to the Column which represents my range. When creating a reference I select a column in my source sheet as my range. That is what I mean by unique column. I am only using index/match, therefore only one column, not like Vlookup where you could have multiple columns.

I believe there is a linear formula, as somehow a piece of software is calculating the 100,000 limit. I do understand what you mean by 3 references in one cell and 20 in another, which would give you 23 references to 23 columns in source sheet(s). Therefore if my source sheet contains 1000 rows, the cell link total would be 23,000, correct?

The only thing I am trying to find out, do I have too include the the number of rows and columns which contains references in my destination sheet, or is it only about the incoming data from the source sheets?

MichaelTCA

@JBG

You would add 1 reference to all existing references and an additional for the new reference.

I see it as, if you had 1 column with 23 references, and you add 1 more row in your source sheet, 1001 for example. You now have 23000+(#rows*new references)+(#new rows*new references). You would end with 24024 references. The trick will be to know exactly how many references are contained within that individual cell.

It's much easier to have an element count of the source code than developing a function that doesn't interact with that data directly.

You actually can specify ranges exactly the same as vlookup and easier to use, in my opinion. I've been using Index/Match since day 1.

MichaelTCA

@JBG

Does that make sense?

In your original post, you are starting with 40 references per cell. I'll assume that is 1 column and 1000 rows. Total, you have 40,000 cell references on the target sheet.

If you add 1 new row (adding a new reference) to the source sheet, you will now have 40000+(1000*1)+(41*1), which equals a total of 41041.

If you plan on adding about 5500 references in 1 year, than you will have 40000+(1000*5500)+(1*5540)