Checking off a box in one form when another is filled out?

Hello! I am very, very new to Smartsheet so please forgive me if this isn't the right place to ask or a dumb question.

I am attempting to make a sheet that records when someone has filled out a form on another sheet. The original sheet is a form for a customer service survey, and the one I want to make records when and if specific people have filled that survey out.

I want to make it so that the new sheet can see if someone's name has appeared on the customer service survey for the month, the second form will record that and check off a checkbox. I have no idea where to even begin to do this, or if it's possible.

Sheet 1:

Brief idea of sheet 2:

Thanks so much!

Best Answer

  • Hunter Taylor
    Hunter Taylor ✭✭
    Answer ✓

    Hi Sorodrib,

    You can accomplish this in Smartsheet using a combination of cross-sheet formulas and automation. Here's a step-by-step guide to get started:

    1. Set Up the Customer Service Survey Sheet

    • Ensure that the survey form submissions populate rows in a sheet (let's call it "Survey Sheet").
    • Make sure each row captures the person's name and the date of submission. You might already have these columns if it’s set up as a form.

    2. Create the Tracking Sheet

    • In the second sheet (let's call it "Tracking Sheet"), create a list of names you want to track in one column.
    • Next to each name, create a checkbox column (e.g., "Survey Completed").

    3. Use a Cross-Sheet Formula

    • In the "Survey Completed" column of the Tracking Sheet, add a formula that checks the Survey Sheet for each person’s name for the current month.
    • =IF(COUNTIF({Survey Sheet Name Column}, [Name]@row) > 0, 1, 0)
    • Replace {Survey Sheet Name Column} with a cross-reference to the name column in the Survey Sheet.
    • You might need to add a condition to filter by the current month, which can be done using functions like MONTH and YEAR if your sheet includes a date column.

    4. Automate Monthly Reset (Optional)

    • If you want to reset checkboxes each month, set up an automation in Smartsheet to clear the checkbox column at the beginning of each month.

    This setup will give you a real-time view of who has filled out the survey each month based on the Survey Sheet data. Let me know if you need more detailed steps for any part of this setup!

    Ask Me About Smartsheet Maps?!?!

    Solving Automation, Integration, & Adoption Problems For Smartsheet Customers

    Account Executive | Skyway Consulting Co

    Explore The Possibilities of Smartsheet & ArcGIS

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use a COUNTIFS to see how many times that person has filled it out for the date range of your choice. Then you would use an IF to say that IF the COUNTIFS is greater than zero, check the box.

    =IF(COUNTIFS(……………………), 1)

  • Hunter Taylor
    Hunter Taylor ✭✭
    Answer ✓

    Hi Sorodrib,

    You can accomplish this in Smartsheet using a combination of cross-sheet formulas and automation. Here's a step-by-step guide to get started:

    1. Set Up the Customer Service Survey Sheet

    • Ensure that the survey form submissions populate rows in a sheet (let's call it "Survey Sheet").
    • Make sure each row captures the person's name and the date of submission. You might already have these columns if it’s set up as a form.

    2. Create the Tracking Sheet

    • In the second sheet (let's call it "Tracking Sheet"), create a list of names you want to track in one column.
    • Next to each name, create a checkbox column (e.g., "Survey Completed").

    3. Use a Cross-Sheet Formula

    • In the "Survey Completed" column of the Tracking Sheet, add a formula that checks the Survey Sheet for each person’s name for the current month.
    • =IF(COUNTIF({Survey Sheet Name Column}, [Name]@row) > 0, 1, 0)
    • Replace {Survey Sheet Name Column} with a cross-reference to the name column in the Survey Sheet.
    • You might need to add a condition to filter by the current month, which can be done using functions like MONTH and YEAR if your sheet includes a date column.

    4. Automate Monthly Reset (Optional)

    • If you want to reset checkboxes each month, set up an automation in Smartsheet to clear the checkbox column at the beginning of each month.

    This setup will give you a real-time view of who has filled out the survey each month based on the Survey Sheet data. Let me know if you need more detailed steps for any part of this setup!

    Ask Me About Smartsheet Maps?!?!

    Solving Automation, Integration, & Adoption Problems For Smartsheet Customers

    Account Executive | Skyway Consulting Co

    Explore The Possibilities of Smartsheet & ArcGIS

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!