Generate new row when box is checked



The sheet I'm using is the back end of a sign-in form for my office. The user selects the week they want to come in and checks a box next to the days of the week. I then have a series of formulas that will turn the checked box into its corresponding date. What I would like is for each of those dates to show as its own row and the name of the person signing in. I'm assuming this would have to appear in a separate sheet which is fine by me. This is after I retooled the form. The old format (the one I want back) is below.

This allows me to see all the people who signed in on a given day, making that info easy to export in the event of the need to contact trace.

Here's what I'm working with now. The form that generates this is good for the user and I'd like to keep it.

In order from left to right: Name, Week-Of (date), Blank column (disregard), Monday, Tuesday, Wednesday, Thursday, Friday (blank because no ones wants to come in on Fridays). I'm looking to automatically generate a new row for each of those Monday-Friday dates (which are generated with a formula corresponding to the checkboxes) to match the format in the first picture. Is there a formula or combination of formulas I could use to accomplish this?



  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 03/10/22

    You can't use a formula to create a row, unfortunately. Here's a bit of an ugly workaround you could use.

    First, set up an auto-number row on your initial page, so each line is given a unique entry.

    Then, set up five Workflows, one for each day of the week. They'll all look the same, except for the day column they reference. Here's the Monday example:

    When: [Name] change to any value, AND [Monday] is a date, THEN COPY the row to {New Page}

    When the form is submitted and an entry is populated in your original page, above, you will get a workflow triggered for each day of the week that the user has entered. So if they've signed up for Monday, Tuesday, and Thursday, then the workflows for each of those days will trigger and send one copy of the row, for a total of three rows.

    In the new page you created, you'll need to create a formula to group the entries. You'll use the unique row ID from the original page to group them, then then you'll need to create a formula to populate the dates consecutively for each of the entries.

    Good luck!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!