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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @Vinton Douglas

    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.

    https://app.smartsheet.com/b/publish?EQBCT=50617d5a09a24d1e87957c3cc7c399f8

    Step 1: Add a Sheet Summary Field

    1. In your source sheet, go to Sheet Summary.
    2. 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

    1. In the source sheet, add a new column named Transfer.
    2. Set the column type to Checkbox.
    3. bashCopy code=[Transfer Trigger]#This links the checkbox to the Transfer Trigger field in the Sheet Summary.

    Step 3: Create Workflow Automation

    1. Go to Automation > Create a Workflow.
    2. 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)

    1. 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.
    2. 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!