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

imtonydillard

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.  

Genevieve P.

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.

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

imtonydillard

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.

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)

imtonydillard

@Genevieve P.

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

Cheers,

Tony