Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    "Boolean Expected" Error

    ✭✭✭✭
    in Formulas and Functions

    Hello All,

    I spent a few hours building an equation (I am not an expert) within a sheet that reads another main sheet.

    =IFERROR(INDEX(COLLECT({GenFertCalc}, {Client Name}, [Client Name]@row, {Tones}, "Granular Fertilizer (Tone)"), 1), 0)

    The error message "Boolean Expected" occurs in the correct cells, but instead of marking a check box, the error message appears.

    What am I missing, and thanks in advance. :)

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      edited 01/20/25 Answer ✓

      It sounds like you are attempting to either check or not check a box based on the content of another sheet. The way your formula is structured, you will be returning the value of {GenFertCalc}. Unless this value is 1 or 0, you will get the boolean error.

      Can you give a description of what you are attempting to accomplish, or even better, post a screenshot of the source and target sheets?

      Edit:

      Taking a stab at it before you respond… is this what you are trying to do?

      =IF(COUNTIFS({Client Name}, [Client Name]@row, {Tones}, "Granular Fertilizer (Tone)") > 0, 1, 0)

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭
      edited 01/20/25

      I forgot to mention that some of the items did mark the box checked while others had the error message but all items with the checked box or the message were correctly noted.

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 01/20/25 Answer ✓

      It sounds like you are attempting to either check or not check a box based on the content of another sheet. The way your formula is structured, you will be returning the value of {GenFertCalc}. Unless this value is 1 or 0, you will get the boolean error.

      Can you give a description of what you are attempting to accomplish, or even better, post a screenshot of the source and target sheets?

      Edit:

      Taking a stab at it before you respond… is this what you are trying to do?

      =IF(COUNTIFS({Client Name}, [Client Name]@row, {Tones}, "Granular Fertilizer (Tone)") > 0, 1, 0)

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      OMG. you got it! Absolutely amazing how you experts see this "language" as clear as I read English. Much appreciated.

      My goal is to report that something was completed by employees for particular clients. In this case it was using a particular fertilizer whose name includes "tone". Clearly I was going about it in a more complicated way.

      Again, thank you, thank you.

      · Share on FacebookShare on Twitter
    • Community Champion

      Always happy to help!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions