I am unable to find the right formula to checkbox in a column?

Sheet 1 - I receive project feedback for various projects based on project ID numbers and names. This is the sheet I want to pull data from.

Sheet 2 - This where I need to track whether feedback is received or not based on a column checkbox to reference Sheet 1.

Q1: What would be a good formula to pull data from Sheet 1 to Sheet 2 to checkbox?

Q2: When there are multiple feedbacks for the same project in Sheet 1, I still want to checkbox and not have BOOLEAN Error, How do I go about that?

Find more posts tagged with

Accepted answers

Bassam Khalil

Hi @Khadija Ali

Please try the following:

use the COUNTIFS function to check if the project ID in Sheet 2 matches any project ID in Sheet 1 and if there's any entry under the feedback-related columns. Here’s an example formula you can use in Sheet 2:

=IF(COUNTIFS({Sheet1 Project ID Range}, [Project ID]@row) > 0, 1, 0)

In this formula:

{Sheet1 Project ID Range} should be replaced with the range of the "Project ID" column in Sheet 1.
[Project ID]@row refers to the Project ID on the current row of Sheet 2.

This formula checks if there are any rows in Sheet 1 that have the same Project ID as the current row in Sheet 2. If at least one match is found, it returns 1 (which will check the box), otherwise, it returns 0 (which will leave the box unchecked).

The formula provided above inherently handles multiple feedbacks per project without returning a BOOLEAN error. The COUNTIFS function counts the number of times the specified condition is met across the dataset. As long as there's at least one feedback for a project, the checkbox will be checked, regardless of the number of feedback entries. This approach doesn't throw a BOOLEAN error because it's always returning a numeric count, and the IF function then converts this count into a checkbox state (checked or unchecked).

All comments

Andrée Starå

Hi @Khadija Ali

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Khadija Ali

Hello@Andrée Starå

Sheet 1: This is the sheet I get all my feedback and would like to use as my reference sheet

Sheet 2 : This is the sheet I want to create the formula for Sponsor Feedback Received Column?

I hope this helps give more context!

Bassam Khalil

Hi @Khadija Ali

Please try the following:

=IF(COUNTIFS({Sheet1 Project ID Range}, [Project ID]@row) > 0, 1, 0)

In this formula:

{Sheet1 Project ID Range} should be replaced with the range of the "Project ID" column in Sheet 1.
[Project ID]@row refers to the Project ID on the current row of Sheet 2.

Khadija Ali

@Bassam Khalil It worked! Thank you.

Bassam Khalil

That's Good news @Khadija Ali don't hesitate to call for any help.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.