Reference Formula Across Sheets

Hello,

I have been working with this formula below in order to search multiple sheets to see if a name has been submitted before. The formula is currently in a checkbox format and I keep getting the error "Boolean Expected". I think it wants to display a number but what I want it to do is check the box if it has appeared on any of the other sheets.

=IF(COUNTIF({Sample Range 1}, [Name Field]@row) > 0, 1, 0) + IF(COUNTIF({Sample Range 2}, [Name Field]@row) > 0, 1, 0) + IF(COUNTIF({Sample Range 3}, [Name Field]@row) > 0, 1, 0)

Any suggestions?

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @ALEX BOSTROM

The error is coming through because you're adding together the IF statements. This means that if all three are correct, the output is actually 3 (1 + 1 + 1) instead of 1.

Try adding all the separate COUNTIFS together first, then you can do one large overall IF statement to say if the total of all these sheets is greater than 0, check the box, like so:

=IF(COUNTIF({Sample Range 1}, [Name Field]@row) + COUNTIF({Sample Range 2}, [Name Field]@row) + COUNTIF({Sample Range 3}, [Name Field]@row)) > 0, 1, 0)

Does that make sense?

Cheers!

Genevieve

ALEX BOSTROM

Thank you, it worked perfectly! This was very helpful!!

Genevieve P.

No problem at all! I'm glad I could 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.