Check a box if multiple columns contain an entry

I would like to create a column with a formula that will check a box if several of the following columns contain an entry (any kind of entry). The application for this equation is to confirm completion of a registration form. The sheet will be completed by registrants and I would like to confirm that ~10 registration questions have been completed. The column with the equation will be called 'Registration Complete' and will check if there is an entry in the next ~10 columns (since each registration question column has different properties, text, numbers, dropboxes, etc I am not looking for any specific criteria for what is inserted in the column, only yes/no is there data entered in the cell). Please let me know if there is a solution for this? I have found equations that can search for specific text, but here I am not looking for specific text and I am also looking for the equation to confirm values are input in a range of columns.

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Karenmst

The only way that the applicant will know the form is complete is to Require each of the questions on the form.

You do this in the form designer, using Field Settings

Yes, there is a formula you can use but the calculation will not be made until AFTER the form is submitted. Calculations happen in the sheet and until data is submitted, the sheet doesn't know the data exists.

For the calculation, your data range will begin with the first of your ~10 columns. I'll call that Col1. Your range ends with the last of your ~10 columns. I'll call it Col10. It doesn't have to be 10 columns but you will need to know the exact count you are looking for.

=IF(COUNT([Col1]@row:[Col10]@row)=10, 1)

This looks to see if there are enough non-blank cells (COUNT only counts non-blanks) and if it meets the criteria, will mark the checkbox as checked.

Will this work for you?

Kelly

Karenmst

Yes! Thank you so much. Using the 'Required' feature of the form fixes my problem for data that is entered through the form. And the Calculation you provided gives me a tool to add a data check to columns of my sheet that may be managed internally within the sheet. Thank you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.