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

Marlana K.
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!


Sr. Business Analysts / Smartsheet Solutions

National Pharmacy Services | Genoa Healthcare