Formular: Only two requests each department per week

Gregory.Jack

Hi Community,

I have a sheet request form that capture transport requests from each department. Heres my scenerio:

For my organsiation a request for transport is allowed only 2 request per week for each department. Requests more than two is automatically cancelled for the department.

what formula can I use to a helper column to identify if a 3rd request has been submitted to automate an email notifying user request that this request has been cancelled.

jpaul

Hey Gregory,

This one hurt my head for a second but I finally figured out a work around for you. I am assuming you are having them identify their department within the form, and then a column that identifies the Date entered.

Create an auto number column in your sheet.

Then create a column titled "Work week and place the below formula into it:

=WEEKNUMBER([Date entered]@row)

Next create a column title "Request this week" and put the below formula in it.

=COUNTIFS(Department:Department, Department@row, [Row ID]:[Row ID], <=[Row ID)]@row, [Week Number]:[Week Number], [Week Number]@row)

Make that a column formula.

Below is a screen shot of how I have it set-up. This functions so it only counts itself plus past entries by that same department within the same work week.

You can hide all the helper columns like "Work Week" and the "ROW ID" to clean your sheet up.

You can also set-up an automation to alert the form submitter if their "Request this week" number is 3 or greater.

If you are not familiar with automations let me know and I can throw some screen shots together for you.