Cross reference cell limitation of 100,000

JBG
JBG ✭✭✭
edited 05/17/24 in Smartsheet Basics

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

Answers

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    edited 05/17/24

    @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
    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
    MichaelTCA ✭✭✭✭✭✭
    edited 05/23/24

    @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
    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
    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
    MichaelTCA ✭✭✭✭✭✭
    edited 05/24/24

    @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
    MichaelTCA ✭✭✭✭✭✭
    edited 05/24/24

    @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)