#BOOLEAN EXPECTED Error

I have 2 sheets with check boxes when the checkbox "Done" is checked on the first sheet a check box called "Open" unchecks on the 2nd sheet. The formula I use is

=COUNTIFS({Analyst Request Tracker with Form Range 2}, [Task Name]@row, {Analyst Request Tracker with Form Range 3}, <>1)

I keep getting a "#BOOLEON EXPECTED" ERROR. How can this be addressed so stops happening?

Schedule sheet:

Request Tracker:

All of these tasks should have a check box in the Open column.

Find more posts tagged with

Accepted answers

Brian_Richardson

Oh, your formula has an extra comma in it. Take out the comma right before the >0 part.

All comments

Brian_Richardson

Your request tracker checkbox column probably has a checkbox cell somewhere where someone entered some text instead of checking the box.

ClanMcDe

https://community.smartsheet.com/discussion/comment/455893#Comment_455893

I've checked for this and nothing other than checks.

TVang

@ClanMcDe, the formula you're using in the Open column is returning something other than 0 or 1 for that row (0=false, 1=true). That is why you're getting the #BOOLEAN EXPECTED error.

The task on that row from "Schedule Sheet" appears several times in the "Request Tracker" sheet.

Brian_Richardson

Actually, I think your formula may be returning a count greater than 1 for those cells. Try wrapping it in an IF statement to place true or false in the column.

=If(COUNTIFS({Analyst Request Tracker with Form Range 2}, [Task Name]@row, {Analyst Request Tracker with Form Range 3}, <>1)>0,true)

ClanMcDe

I believe I found (stumbled on) the answer, In the second sheet if a task is duplicated then the checkbox is displaying the #BOOLEANEXPECTED error. when the duplicate item is removed on the referenced sheet the error goes away.

Brian_Richardson

Right- the last formula I provided handles that by checking the box when the count is greater than 0, so it can deal with duplicates etc.

ClanMcDe

https://community.smartsheet.com/discussion/comment/456843#Comment_456843

Hi Richard, I tried your suggestion and now getting an invalid data type error instead.

Brian_Richardson

It needs to be in a checkbox column

ClanMcDe

https://community.smartsheet.com/discussion/comment/460966#Comment_460966

Hi Richard, That's the thing, it is in a checkbox column. I'm just not sure why it does not like it. The formula is in a checkbox column looking at a checkbox column.

=IF(COUNTIFS({Periscope Request Tracker Range 1}, [Task Name]@row, {Analyst Request Tracker with Form Range 3}, <>1), >0, true)

Brian_Richardson

Oh, your formula has an extra comma in it. Take out the comma right before the >0 part.

ClanMcDe

Ahhh, That worked. Thanks for all your help Richard, it is greatly appreciated!

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.