Understanding 100,000 Cell Reference Limits and Activity Log Cell Changes

Marlana K.

We are trying to determine if we have an issue with several sheets that appear not to be updating the information indexed into the sheet from a reference sheet until opened. We have read through the community here trying to make sense of the 100,000 cell limit. However after reading and looking at the Activity log for several sheets that math just doesn't add up/match. Looking for some help understanding what is going on.

Process outline

1. We have an API setup that pulls Pharmacy Data from our data base and populates 25 Columns in our Pharmacy Site Information (API) sheet and 10 columns in our Pharmacy Region Information (API) sheet. The sync runs each night at 2 AM PT, deleting all data and repopulating the sheets. This Pharmacy Site Information (API) sheet will continue to grow as we are continuously opening new sites (currently 764 rows).
2. This sheet is used to populate other sheets through indexing. Most sheets do not index all columns in the sheet, they range from 4-12 of the 25 columns available.
   1. Example we have several Solutions that uses an Intake form and a Master Site List. This is what we call a "burn down" process. The sheets work in tandem, the form is used to upload their certificate/checklist upon renewal and the Master Site List is basically used to track that the sites completed the task. Makes the process really easy to see who is not compliant.
   2. Using one of these Solutions (Certificate Upload) here is what we are indexing
      1. 5 Columns index the Intake Form/Certificate Upload information sheet
      2. 12 Columns index the Pharmacy Site Information (API) Sheet
      3. 4 Columns index the Pharmacy Region Information (API) Sheet
   3. Based on this that is 21 cells in each row 1178 rows that require updates my math says 21*1178=24738 cell updates. However the activity log shows Cells changed as a variety of numbers. Also on May 2 and May 28 they just stopped and did not complete any updates after rows 605

Any insight would be appreciated as I am a bit perplexed and trying to figure out why several sheets are not updating until opened when they all use the same formulas for indexing is putting me at a loss for reason.

I read the below articles too!

https://community.smartsheet.com/discussion/comment/325900#Comment_325900

https://community.smartsheet.com/discussion/78708/100-000-cell-reference-limit-do-i-understand-it-right-and-can-i-monitor-it

Jeff Reisman

Do any of the triggers rely on formulas that use the TODAY function? Many times the value of TODAY does not update until the sheet is opened.

What I have done to get around this is using a "Current Date" column that I update at 1am with a Record a Date automation. Then I use that value when I need to consider the current date in a formula. This updates no matter if the sheet is opened or not.

Marlana K.

@Jeff Reisman

We have tried all that in the first sheet that we discovered not working and it still doesn't update. Now a sheet that has worked for over two years suddenly stopped working.

Jeff Reisman

Are there broken cross sheet references? That can happen from time to time. Right click on any cell and choose Manage References, verify each one is still working.

Marlana K.

Thanks for the suggestion (that was my next thought to go undo and redo all of the indexing) a pain but it is worth a shot even though one sheet was actually rebuilt from scratch and repeats the same behavior.

Marlana K.

@Jeff Reisman I learn something new everyday!

Marlana K.

@Jeff Reisman throw that theory out none listed under the broken option. Any other suggestions?