Is there a way to check a box in a sheet based on data and checkbox in another sheet?

In this scenario, Index Smartsheet automation is to happen based on data in the Licensing Smartsheet. More specifically - Check box in Ready To Start E. cell in Index Smartsheet when Licensing Smartsheet States To Be Filed (checkbox) is checked and ALL states to be filed Filing Status's equal “Complete” and ALL states to be filed State is one of or equal to or matches state in Required States column.  

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @imtonydillard

    The second formula should only check to see if the Required States column has the values that the JoinCollect formula brought back... even if those values are selected with other ones.

    Did you try this?

    =IF(HAS([Formula Column]@row, [Required States]@row), 1, 0)


    In my image, the "Formula Column" is called "JoinCollect" so my formula would be:

    =IF(HAS(JoinCollect@row, [Required States]@row), 1, 0)

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @imtonydillard

    Yes!

    Assuming that the "Required States" column is a multi-select column, what we can do is add in another helper column to your "Index Smartsheet" sheet.

    Make the helper column another multi-select column, then we can use a JOIN(COLLECT formula to bring back all of the States that meet your criteria into one cell (the same cell, copied down the entire sheet).

    Then we use the HAS function to see if this cell that lists every State has all of your "Required States" listed for that row.

    Here's an example where I built this in the same sheet. Pretend the blue columns are in a different sheet:


    A cross-sheet JOIN(COLLECT column formula would looks something like this:

    =JOIN(COLLECT({States Column}, {Filing Status Column}, "Complete", {Sates to be filed Column}, 1), CHAR(10))

    The CHAR(10) at the end is what separates your values into a multi-select value.


    Then in your checkbox column, try a formula like this:

    =IF(HAS([Formula Column]@row, [Required States]@row), 1, 0)


    Cheers,

    Genevieve

  • Hi @Genevieve P. I am sorry for the delayed response. Thanks for the recommendation. It works, but...

    In the instance where Required States row containing AL, AK, AR, CO, I need the Ready box to be checked. My reason for this is because AL , CO & CT need to be ignored since they are not checked in States To Be Filed. Is this possible?


    Tony

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @imtonydillard

    The second formula should only check to see if the Required States column has the values that the JoinCollect formula brought back... even if those values are selected with other ones.

    Did you try this?

    =IF(HAS([Formula Column]@row, [Required States]@row), 1, 0)


    In my image, the "Formula Column" is called "JoinCollect" so my formula would be:

    =IF(HAS(JoinCollect@row, [Required States]@row), 1, 0)

  • @Genevieve P.

    Mega thanks for your help with my need. IT WORKED! You are awesome.

    Cheers,

    Tony

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!