Understanding 100,000 Cell Reference Limits and Activity Log Cell Changes
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
- 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).
- 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.
- 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.
- Using one of these Solutions (Certificate Upload) here is what we are indexing
- 5 Columns index the Intake Form/Certificate Upload information sheet
- 12 Columns index the Pharmacy Site Information (API) Sheet
- 4 Columns index the Pharmacy Region Information (API) Sheet
- 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!
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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 KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
@Jeff Reisman I learn something new everyday!
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
@Jeff Reisman throw that theory out none listed under the broken option. Any other suggestions?
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives