Flagging a cell based on checkmarks

Options

Hello,

I need a cell to flag if there is a check mark within a range of cells. I currently have a box checked if the schedule date matches a date in another column. The box checks if the Install Date matches the test date so I am thinking that if there are any check boxes within those columns I should raise the flag. At a loss as to how to go about doing this.

Any help would be greatly appreciated.

Sharon C

Best Answer

Answers

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭
    Options

    This is the formula I have tried thus far and I am getting an #Unparsable error.

    =IF([Test Install Date]@row = "", "", IF(AND(VLift Conflict]@row = 1, OR([VStore Conflict]@row = 1, [Chase Conflict]@row = 1), True, False))

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this:

    =IF(ISBLANK([Test Install Date]@row), "", IF(OR([VLift Conflict]@row = 1, [VStore Conflict]@row = 1, [Chase Conflict]@row = 1), "True", "False"))

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭
    Options

    PERFECT!!! Oh my gosh, thank you so very much. Santa should be VERY, VERY good to you. If I could give you ten stars I would

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    edited 12/15/20
    Options

    I read it as to cause the trigger you need a Date, a Vlift Conflict filled in and either a V Store Conflict or a Chase Conflict:

    =IF([Test Install Date]@row = "", 0, IF(AND([VLift Conflict]@row = 1, (OR([Chase Conflict]@row = 1, [VStore Conflict]@row = 1))), 1, 0))

    In order to trigger if there are any checkboxes with a date:

    =IF([Test Install Date]@row = "", 0, IF(OR([VLift Conflict]@row = 1, [Chase Conflict]@row = 1, [VStore Conflict]@row = 1), 1, 0))

    Be careful with your "", Trues and Falses.. especially with checkboxes and symbols.. also watch the brackets.. Also, remember your cell type has to be a checkbox or symbol to evaluate properly

    A free tool I use is Notepad++ which keeps track of the brackets for you. A little bit better then Smartsheet which is better than Excel

    A trick I have learned from Excel and long IF statements is to build it backwards

    For you I started with the : =OR([Chase Conflict]@row = 1, [VStore Conflict]@row = 1) and mad sure that worked

    Then the got the : =IF(AND([VLift Conflict]@row = 1, (OR([Chase Conflict]@row = 1, [VStore Conflict]@row = 1))), 1, 0) working

    I then went to the front and made sure =IF([Test Install Date]@row = "", 0, "Formula") works how I want it

    replaced the "Formula" with =IF(AND([VLift Conflict]@row = 1, (OR([Chase Conflict]@row = 1, [VStore Conflict]@row = 1))), 1, 0) checked the brakets and removed the equals in front of the IF

    This method shows me where it is expecting to say a numeric 1 and you are passing a text "True"

    Hope this helps

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭
    Options

    Lots of good advice. Thank you again

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!