Weekly Project Report

sanchezf

When submitting an automated weekly report that goes out on Monday, sometimes the whomever is submitting the document goes directly into the sheet and use the form from their or the sheet rather than using the form sent out on Mondays, and then this creates another row which messes up the formula that pulls the most updated entries onto a dashboard for executives.

Also, when we move some of the information from the updated column to another column labeled archive (due to what is being reported) it doesn't allow us to continuously move stuff into the column like it has a certain amount of characters it can only take.

Is there a workaround to fix these two issues?

jmyzk_cloudsmart_jp

Hi @sanchezf

Regarding your first question, I suggest limiting users' access as viewers to the sheet that your form adds rows.

From your comment, "the formula that pulls the most updated entries onto a dashboard for executives," I guess you are using the Created or Modified value to determine the "most updated entries." By limiting users' access to viewers, you can avoid this problem.

If users need to edit their entries, I suggest creating a copy of their entries in a separate sheet by automation and giving the users editor access. (Another good practice is requesting an update, but if you use the "Modified" column, the update will mess up your most updated entries formula. )

An easy way to check both the original entry values and user-edited values is to create a report with the master sheet and the copied sheet, grouping by the primary column or Row ID.

If you give an editor access to users, they can see both the master entry and their editable copy and edit the editable ones.

https://app.smartsheet.com/b/publish?EQBCT=4860b03f2c254427a89094c585a466a1

Regarding your second question on the max cell characters limits, I did some experiments and found the limit is 4000 characters. I tried to combine cell values that exceed the limit by formula and get the overflowing part, but I found the function (JOIN, or + ) has the same limitation.

If your question is regarding coping or moving rows to another sheet, say an archive sheet, a sheet does have limitations. I suggest creating separate columns.

500,000 max cells. 20,000 max rows. 400 max columns.

In many cases, the most relevant limitation is the 20,000 max rows. So, I suggest offloading to other media, such as Google Spreadsheets, which can hold up to 10 million cells manually or by Data Shuttle.

https://app.smartsheet.com/b/publish?EQBCT=54b887e00214475fb7aa6528256a2cb7