#BOOLEAN EXPECTED Error

ClanMcDe

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.

Brian_Richardson

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

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.

Toufong Vang

@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!