Checkbox based on Cross sheet Date reference

Options
BBIrr41
BBIrr41 ✭✭✭

Hello,

I am trying to have a checkbox in sheet A auto-check if the date in [Anticipated Transaction Date]@row falls between or is equal to the dates in sheet reference {Blackout Period Tracker Range 1} and sheet reference {Blackout Period Tracker Range 2}.


Help Please!

😅

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Options

    =IF(AND([Anticipated Transaction Date]@row=>{Blackout Period Tracker Range 1},[Anticipated Transaction Date]@row=<{Blackout Period Tracker Range 2}),1)

  • BBIrr41
    BBIrr41 ✭✭✭
    Options

    Thank you @BullandKhmer ! Unfortunately I am getting #INVALID OPERATION. ☹️

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Options

    Yeah that's right. I made a basic syntax error. I'm sure you can figure out what it is.

  • BBIrr41
    BBIrr41 ✭✭✭
    Options

    The syntax error I found didn't resolve the issue but thanks anyways.

    Common issue: <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @BBIrr41

    Are your Range 1 and Range 2 references referring to single cells each, or are both/either referring to a longer range (for instance, a column?)

  • BBIrr41
    BBIrr41 ✭✭✭
    Options

    Hi @Kelly Moore ,

    Range 1 and 2 are referring to a date columns. I have inserted a screenshot for reference.

    Thank you!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @BBIrr41

    I thought that might be the issue. One has to construct IF statements differently when using a cross sheet range, if the range is greater than a single cell. As written, you are asking the IF to do a comparison against the entire column all at once and the formula fails this evaluation.

    IF(COUNTIFS({Blackout Period Tracker Range 1},IFERROR(@cell,0)<=[Anticipated Transaction Date]@row, {Blackout Period Tracker Range 2},IFERROR(@cell,0)>=Anticipated Transaction Date]@row)>0,1)

    Try this

    Kelly

  • BBIrr41
    BBIrr41 ✭✭✭
    Options

    Thank you @Kelly Moore ! That worked, one minor bracket missing but easy enough to find. I appreciate you not just helping, but giving me insight on the why. Have a great day!



    IF(COUNTIFS({Blackout Period Tracker Range 1},IFERROR(@cell,0)<=[Anticipated Transaction Date]@row, {Blackout Period Tracker Range 2},IFERROR(@cell,0)>=[Anticipated Transaction Date]@row)>0,1)

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Oops, good catch. My copy paste wasn't quite on point. I'm glad it worked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!