Creating a flagging process

Vinton Douglas

Hello,

I have created a form to be fed into a mastersheet so that persons should submit twice weekly, 3 days apart. Because many persons will be submitting weekly, I would want the system to flag persons who havent submitted twice for the week. Grateful for any assistance to create such logic.

Paul Newcome

Assuming you have a column that has some sort of unique id for each person, you would use something like this...

=IF(COUNTIFS([Unique ID]:[Unique ID], @cell = [Unique ID]@row, [Submission Date]:[Submission Date], AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Submission Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Submission Date]@row))) > 1, 1)

Vinton Douglas

Hi Paul,

Thanks much. The column would be continuously receiving data from the same set of individuals each week. It will therefore be continuously be populated (twice per wee by each individual). How would we be able to create this unique ID for each person? Also would we be able to display the names of the individuals such that the same name keeps together in the spreadsheet each week, rather than the order in which the forms are sent by individuals?

Vinton Douglas

I was trying to create the columns but not quite understanding. See attached and let me know if this is what you are suggesting.

Testing.png

Devin Lee

Recalling Data from Form Submissions

1. Assuming the Original Sheet collects a Name and Date during the form submission
2. Create a New Sheet (This will be used to flag)
3. Create columns: "Name1", "Date1", and "Date2"
4. You will then create references to your Original Sheet in Date1 and Date2 using LARGE and COLLECT
5. In Date1 use the formula =LARGE(COLLECT(Reference1, Reference2, Name@row), 1)
6. Reference1 is Date:Date of Original Sheet (use the blue Reference Another Sheet that appears when typing followed by clicking on the column header of the Original Sheet)
7. Reference2 is Name:Name of Original Sheet (use the blue Reference Another Sheet that appears when typing followed by clicking on the column header of the Original Sheet)
8. In Date2 use the formula =LARGE(COLLECT(Reference1, Reference2, Name@row), 2)
9. Make sure all Date columns have Column Properties set to Date

From there you should have the two latest dates auto-populated into the New Sheet based on the name in the Name1 column.

Flagging Missing Data

1. Add another column to the New Sheet labeled "Flag"
2. Use the IF and WEEKNUMBER formula to flag the data
3. In Flag use the formula =IF(WEEKNUMBER(Date1@row)+WEEKNUMBER(Date2@row)-WEEKNUMBER(TODAY())-WEEKNUMBER(TODAY())=0,"All Submissions Entered",IF(WEEKNUMBER(Date1@row)+WEEKNUMBER(Date2@row)-WEEKNUMBER(TODAY())-WEEKNUMBER(TODAY())=-1,"One Submission Required",IF(WEEKNUMBER(Date1@row)+WEEKNUMBER(Date2@row)-WEEKNUMBER(TODAY())-WEEKNUMBER(TODAY())<-1,"Two Submissions Required","Error")))

There are many ways to flag the data. This one will display how many submissions they still are required to do. Another option would be to add another row with due dates in Date1 and Date2 and replace TODAY() with references to those cells. I would also recommend using conditional formatting to highlight information when submissions are overdue so it's easier to see.

Good luck!