Have a box checked based on another sheet

Hammermill21

Hello!

I have a main sheet that I would like to have the checkbox checked if a second sheet has a checkbox checked. So if the second sheet has the checkbox checked it would match with the main sheet by the description. Make sense? Any ideas on how I can do this?

Thanks!!

Kelly Moore

Hey @Hammermill21

Let's see if we can't get this to work for you. A screenshot of your actual formula would be helpful.

If you're not already doing this, a good practice is to always rename your cross sheet ranges from the generic smartsheet range number to a name that actually reflects the column you're referencing. This will always help you and the community troubleshoot your formulas. Remember that since these are cross sheet references you cannot simply copy paste this formula into your sheet.

An INDEX MATCH will work if you only have that single criteria (Task Code) to match. To make this work you must make your column type match. In your main sheet you are using a Symbols column. That is not what is giving you the UNPARSEABLE error, but when you get that resolved you would get an INVALID COLUMN VALUE error. When I tested it, Stars or Flags (boolean choices) could work if you didn't want the checkbox

Here's the INDEX Match

=INDEX({source sheet Deactivated}, MATCH([Current Task Code]@row, {source sheet Task Code}, 0))

If you use the IF(Countifs) method, you can keep your symbols by changing the true/false response

IF(COUNTIFS({source sheet Deactivated},1,{source sheet Task Code},[Current Task Code]@row)>0,"Yes", "No")

Do either of these work for you?

Kelly

Kelly Moore

Hi @Hammermill21

The formula below uses COUNTIFS to test if the other sheet. Without you providing information that shows the community how the main sheet row relates to a row in the 2nd sheet, I had to be generic in my answer.

The formula below looks to see if there are any matches based on criteria. If a match is present, the count will be greater that zero. When such a match is present, the formula says to check the checkbox. You may provide more filtering in the COUNTIFS formula, using the syntax (Countifs(range1, criteria1, range2, criteria2, range3, criteria3.....). Thus you will always add a range, criteria pair

=IF(COUNTIFS({2nd sheet checkbox column}, 1, {2nd sheet other column that has relevant information pertaining to 1st sheet}, [information on main sheet]@row)>0, 1)

Hammermill21

Hey KDM,

I tried that but it gives me UNPARSEABLE.

My main sheet looks like this

And this second sheet looks like this:

What relates to both sheets is the Current Task Code & Task Code. I thought maybe using Match/Index might work but I can't get that to function either. So since in the second sheet PLUM-1005C is checked off as deactivated I want the main sheet to match that task code and check it off as deactivated there also.

Kelly Moore

Hey @Hammermill21

Let's see if we can't get this to work for you. A screenshot of your actual formula would be helpful.

If you're not already doing this, a good practice is to always rename your cross sheet ranges from the generic smartsheet range number to a name that actually reflects the column you're referencing. This will always help you and the community troubleshoot your formulas. Remember that since these are cross sheet references you cannot simply copy paste this formula into your sheet.

An INDEX MATCH will work if you only have that single criteria (Task Code) to match. To make this work you must make your column type match. In your main sheet you are using a Symbols column. That is not what is giving you the UNPARSEABLE error, but when you get that resolved you would get an INVALID COLUMN VALUE error. When I tested it, Stars or Flags (boolean choices) could work if you didn't want the checkbox

Here's the INDEX Match

=INDEX({source sheet Deactivated}, MATCH([Current Task Code]@row, {source sheet Task Code}, 0))

If you use the IF(Countifs) method, you can keep your symbols by changing the true/false response

IF(COUNTIFS({source sheet Deactivated},1,{source sheet Task Code},[Current Task Code]@row)>0,"Yes", "No")

Do either of these work for you?

Kelly

Hammermill21

Hey Kelly,

I went with the COUNTIFS to be able to keep my symbols and it's working perfectly.

Thank you so much for all the detail in explaining it to me, I really appreciate it!

Have a wonderful rest of your day!

-Lizzy

lmlearning

Hello @Kelly Moore , I used this formula format: IF(COUNTIFS({source sheet Deactivated},1,{source sheet Task Code},[Current Task Code]@row)>0,"Yes", "No") , and it worked from one sheet (2023 data) to a Main sheet.

Now I want to use the same formula, twice in the Main cell, to pull data from a 2022 sheet as well. Is that possible? Is it an OR function? is it something else?

Using the same logic above I was attempting the following but its not working.

IF(COUNTIFS({source sheet Deactivated 2023},1,{source sheet Task Code 2023},[Current Task Code Main]@row)>0,"Yes", "No"), OR(IF(COUNTIFS({source sheet Deactivated 2022},1,{source sheet Task Code 2022},[Current Task Code Main]@row)>0,"Yes", "No")

Your help is appreciated.

Kelly Moore

Hey @Imlearning

This can be an OR statement or it can be a nested IF statement. Either will work. As written above, the syntax of the formula isn't the OR syntax that smartsheet is expecting.

This is written as an OR. Notice the closing parenthesis before the "Yes" that closes the OR function.

=IF(OR(COUNTIFS({source sheet Deactivated 2023},1,{source sheet Task Code 2023},[Current Task Code Main]@row)>0, COUNTIFS({source sheet Deactivated 2022},1,{source sheet Task Code 2022},[Current Task Code Main]@row)>0), "Yes", "No")

Written as a nested IF since they both end with the same false statement

=IF(COUNTIFS({source sheet Deactivated 2023},1,{source sheet Task Code 2023},[Current Task Code Main]@row)>0,"Yes", IF(COUNTIFS({source sheet Deactivated 2022},1,{source sheet Task Code 2022},[Current Task Code Main]@row)>0,"Yes", "No"))

Kelly