How to set up alerts once the sheet has reached a certain row

gfsp

Hello - I understand there's no capability to limit the number of form responses - only workaround. Can someone please help me set up alerts once a sheet has reached a certain row number? I am setting up a Smartsheet for an event and only have 30 spots available. How do I set up an alert when the form responses reach 30 so I can go in and deactivate the form?

Andrée Starå

https://community.smartsheet.com/discussion/109064/how-to-set-up-alerts-once-the-sheet-has-reached-a-certain-row

Hi,

I hope you're well and safe!

Try something like this. (replace the column name with your Primary Column name)

Add this formula to a so-called helper column. You can name it Counter.

Add a Workflow with an Alert that would trigger when the counter reaches a specific amount.

=COUNT(FormResponse:FormResponse)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

gfsp

I'm confused. So I add a new column and name it "Counter". I then entered the formula you indicated and the result is #unparseable. Is that right?

Where do I find the workflow with an alert that would trigger the counter reaches a specific amount? Can't find it in the workflow templates.

I'm new to Smartsheets so apologies for all the questions. I need someone to break it down for me! Thanks

Andrée Starå

https://community.smartsheet.com/discussion/comment/391032#Comment_391032

Happy to help!

No worries!

You'd have to change the column name to what your Primary Column is named.

Maybe.

=COUNT([Task Name]:[Task Name])

Then you'd add something like this using the, Create Workflow from scratch option.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

gfsp

So I have 6 different columns: First Name, Last Name, Company, Job Title, Email and Phone Number. Do I add a new column and name it Counter? And in the counter column add the formula mentioned in all 30 rows?

I'm not sure what you mean by primary column.

Andrée Starå

https://community.smartsheet.com/discussion/comment/391036#Comment_391036

Yes, but you only need to add the formula to the first row.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

gfsp

I copy and pasted the formula below in row 1 cell of the Counter column and created the workflow you mentioned. #unparseable still shows up when I enter the formula in the cell. To test it out, I also created a 2nd workflow that will alert me anytime a new entry is added. I registered and did not get an alert for that. How long do alerts usually come in?

=COUNT([Task Name]:[Task Name])

A Rose

Hi,

I found the following solution to be the best so far for getting alerted before reaching the cell maximum.

1. Create a column to "Count Cells" as the first sheet column,
   (add a description on another column "Do not delete", and lock the row).
2. Create 2 sheet summary Columns, 1 to "Count Cells" and another to "Count Rows".
3. Under "Count Cells" add a formula to count all cells from the column after "Count Cells" until the last column, including blank and non-blank cells, + the next sheet summary column that counts all rows:
   =COUNTIF([Column #2]:[Last Column], "") + COUNTIF([Column #2]:[Last Column], <>"") + [Count Rows]#
4. In the sheet summary column "Count Rows", add a formula to count all cells in a column that always contains a value, such as an automatic date column or a row count column:
   =COUNT([Date Added]:[Date Added]).
5. In the "Count Cells" column, link the Sheet summary column "Count Cells": =[Count Cells]#.
6. Create a daily alert and filter "Count Cells" to be greater than 400,000.

With this, the sheet summary column counts all blank and non-blank cells, as well as the number of cells in the first column —which can't be included to avoid a circular reference,

And you get an alert BEFORE hitting the 500K cell limit.

PLEASE vote up this Smartsheet idea below - requesting to get an automatic alert!
(Click View post, then vote arrow up).

https://community.smartsheet.com/discussion/137406/alert-before-reaching-smartsheet-sheet-limits

I hope this helps!