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
-
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)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!