Transfer to another sheet
I have a mastersheet populated by a form by multiple individuals on a daily basis. I also have multiple formulas on the same sheet. The sheet often reaches approximately 1000 entries after which some columns in the sheet indicates blocked. Is there a way for this to be resolved? Perhaps transferring some of the data to another sheet when it is full. If so, how can this be arranged automatically?
Answers
-
To resolve the issue of your sheet reaching its limit, you can implement an automatic system to transfer rows to an archive sheet when the number of rows exceeds a specified limit.
Step 1: Add a Sheet Summary Field
- In your source sheet, go to Sheet Summary.
- Add the following fields:
- Max Number of Rows: Set a limit for the number of rows, e.g., 1000.
- Number of Rows: Use the formula
=COUNT([Column Name]:[Column Name])
to count the number of rows. - Transfer Trigger: Use the formula
=IF([Number of Rows]# > [Max Number of Rows]#, 1, 0)
to flag when the number of rows exceeds the limit.
Step 2: Add a "Transfer" Column
- In the source sheet, add a new column named Transfer.
- Set the column type to Checkbox.
- bashCopy code=[Transfer Trigger]#This links the checkbox to the Transfer Trigger field in the Sheet Summary.
Step 3: Create Workflow Automation
- Go to Automation > Create a Workflow.
- Configure the workflow as follows:
- Trigger: When rows are added or changed.
- Condition: When Transfer is checked.
- Action: Move rows to another sheet (e.g., Archive Sheet 1).
Step 4: Set Up Archive Sheet(s)
- In the Archive Sheet 1, replicate the system:
- Add a Max Number of Rows field, setting the limit, e.g., 2000.
- Add a Number of Rows field with the same COUNT formula.
- Add a Transfer Trigger field with the same formula logic.
- Add a Transfer column and link it to the Transfer Trigger.
- Create automation in Archive Sheet 1 to move rows to Archive Sheet 2 when the row limit is exceeded.
Notes for Archive Sheet 2
Since Archive Sheet 2 does not require active formulas, you can set a higher limit for rows (e.g., 10,000) for longer-term storage. Ensure this sheet still allows formulas to function without performance issues.This approach will keep your source sheet under the row limit, ensuring formulas remain functional and automating data management to prevent manual intervention.
Activity Logs
The activity log below shows that Sheet Summary field values change with additional rows, then [Tranfser] is checked, and automation moves the rows to another sheet.
The activity log for the Archive 1 sheet shows that rows are moved from another sheet, the Sheet Summary field values changed, and the workflow automation moved the rows to another sheet(Archive sheet 2).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!